主页 / dba / 问题 / 281861
Accepted
Zoinky
Asked: 2020-12-20 08:30:01 +0800 CST

如何将中位数添加到此查询？

  • 772

我想将中值添加到以下查询中，中值显然适用于每种类型的卧室 unittypeid （已经在 group by 中），我尝试使用 PERCENTILE_CONT 但我无法弄清楚如何使其工作。

CREATE TABLE #TempListings
(
    ListingId int,
    Price money,
    UnitTypeId int,
    BedroomsAvailable int
)
INSERT INTO #TempListings VALUES(1, 1000, 1, 1)
INSERT INTO #TempListings VALUES(2, 2000, 1, 1)
INSERT INTO #TempListings VALUES(3, 3000, 1, 1)

INSERT INTO #TempListings VALUES(4, 1000, 1, 2)
INSERT INTO #TempListings VALUES(5, 2000, 1, 2)
INSERT INTO #TempListings VALUES(6, 3000, 1, 2)

INSERT INTO #TempListings VALUES(7, 1000, 2, 1)
INSERT INTO #TempListings VALUES(8, 2000, 2, 1)
INSERT INTO #TempListings VALUES(9, 3000, 2, 1)

INSERT INTO #TempListings VALUES(10, 1000, 2, 2)
INSERT INTO #TempListings VALUES(11, 2000, 2, 2)
INSERT INTO #TempListings VALUES(12, 3000, 2, 2)
  
SELECT BedroomsAvailable, 
    COUNT(listingid) AS Count, 
    MIN(price) AS MinPrice, 
    MAX(price) AS MaxPrice, 
    AVG(price) AS AveragePrice,
    STDEV(price) as StandardDeviation,
    UnitTypeId
FROM #TempListings
GROUP BY BedroomsAvailable, UnitTypeId
ORDER BY UnitTypeId

DROP TABLE #TempListings

如果重要，我正在使用 sqlserver 2019

sql-server t-sql

2 个回答

  1. Best Answer

    您只想解释语法是如何工作的。但作为完整的查询

    CREATE TABLE #TempListings
(
    ListingId int,
    Price money,
    UnitTypeId int,
    BedroomsAvailable int
)
INSERT INTO #TempListings VALUES(1, 1000, 1, 1)
INSERT INTO #TempListings VALUES(2, 2000, 1, 1)
INSERT INTO #TempListings VALUES(3, 3000, 1, 1)

INSERT INTO #TempListings VALUES(4, 1000, 1, 2)
INSERT INTO #TempListings VALUES(5, 2000, 1, 2)
INSERT INTO #TempListings VALUES(6, 3000, 1, 2)

INSERT INTO #TempListings VALUES(7, 1000, 2, 1)
INSERT INTO #TempListings VALUES(8, 2000, 2, 1)
INSERT INTO #TempListings VALUES(9, 3000, 2, 1)

INSERT INTO #TempListings VALUES(10, 1000, 2, 2)
INSERT INTO #TempListings VALUES(11, 2000, 2, 2)
INSERT INTO #TempListings VALUES(12, 3000, 2, 2)

INSERT INTO #TempListings VALUES(13, 5000, 2, 1)
INSERT INTO #TempListings VALUES(14, 6000, 2, 1)
INSERT INTO #TempListings VALUES(15, 7000, 2, 1)

INSERT INTO #TempListings VALUES(16, 8000, 2, 2)
INSERT INTO #TempListings VALUES(17, 9000, 2, 2)
INSERT INTO #TempListings VALUES(18, 10000, 2, 2)
GO

    WITH #myselect
 AS (SELECT 
         BedroomsAvailable
         ,ListingId 
         ,UnitTypeId
         ,Price,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Price)  
         OVER (PARTITION BY UnitTypeId) AS MedianCont
    FROM #TempListings)
SELECT BedroomsAvailable 
    ,COUNT(listingid) AS Count 
    ,MIN(price) AS MinPrice 
    ,MAX(price) AS MaxPrice 
    ,AVG(price) AS AveragePrice
    ,STDEV(price) as StandardDeviation 
    , MIN(MedianCont) MedianCont
    ,UnitTypeId
FROM #myselect
GROUP BY BedroomsAvailable, UnitTypeId
ORDER BY UnitTypeId   
GO

    卧室可用 | 计数 | 最低价格 | 最高价格 | 平均价格 | 标准差 | 中位数 | 单位类型 ID
----------------: | ----: | --------: | ---------: | ------------: | ----------------: | ---------: | ---------:
                1 | 3 | 1000.0000 | 3000.0000 | 2000.0000 | 1000 | 2000 | 1
                2 | 3 | 1000.0000 | 3000.0000 | 2000.0000 | 1000 | 2000 | 1
                1 | 6 | 1000.0000 | 7000.0000 | 4000.0000 | 2366.43191323985 | 4000 | 2
                2 | 6 | 1000.0000 | 10000.0000 | 5500.0000 | 3937.00393700591 | 4000 | 2

    db<>在这里摆弄

    • 2

  2. 在 SQL Server 中有多种计算中位数的方法。Aaron Bertrand 在What Is The Fastest Way To Calculate the Median？.

    如果你想使用 PERCENTILE_COUNT 你可以这样做：

    SELECT BedroomsAvailable, UnitTypeId, PERCENTILE_CONT(0.5) AS PriceMedian
WITHIN GROUP (ORDER BY price) OVER (PARTITION BY BedroomsAvailable, UnitTypeId)
FROM #TempListings

    然后，您可以将其加入到您的主查询中BedroomsAvailable和UnitTypeId。

    例如：

    WITH CTE_PriceMedians AS
(
    SELECT BedroomsAvailable, UnitTypeId, PERCENTILE_CONT(0.5) AS PriceMedian
        WITHIN GROUP (ORDER BY price) OVER (PARTITION BY BedroomsAvailable, UnitTypeId)
    FROM #TempListings
)

SELECT BedroomsAvailable 
    ,COUNT(listingid) AS Count 
    ,MIN(price) AS MinPrice 
    ,MAX(price) AS MaxPrice 
    ,AVG(price) AS AveragePrice
    ,STDEV(price) as StandardDeviation 
    ,MIN(MedianCont) MedianCont
    ,UnitTypeId
    ,PriceMedian
    ,SUM(price)/COUNT(1) AS PriceMean
FROM #TempListings AS TL
INNER JOIN CTE_PriceMedians AS PM
    ON TL.BedroomsAvailable = PM.BedroomsAvailable
    AND TL.UnitTypeId = PM.UnitTypeId
GROUP BY BedroomsAvailable, UnitTypeId
ORDER BY UnitTypeId
    • 1

相关问题