Início / dba / Perguntas / 281861
Accepted
Zoinky
Asked: 2020-12-20 08:30:01 +0800 CST

Como adiciono Median a esta consulta?

  • 772

Eu gostaria de adicionar median à seguinte consulta, a mediana obviamente será para cada tipo de quarto, unittypeid (já no grupo por), tentei trabalhar com PERCENTILE_CONT, mas não consegui descobrir como fazê-lo funcionar.

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

Estou usando o sqlserver 2019 se for importante

sql-server t-sql

2 respostas

  1. Best Answer

    Você queria apenas explicar como a sintaxe funciona. Mas como consulta completa

    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

    QuartosDisponível | Contagem | Preço mínimo | MaxPrice | Preço Médio | Desvio Padrão | MedianCont | UnitTypeId
----------------: | ----: | --------: | ---------: | -----------: | ----------------: | ---------: | ---------:
                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 | 10.000,0000 | 5500.0000 | 3937.00393700591 | 4000 | 2

    db<>fique aqui

    • 2

  2. Há várias maneiras de calcular a mediana no SQL Server. Aaron Bertrand percorre os principais e compara seu desempenho em Qual é a maneira mais rápida de calcular a mediana? .

    Se você quiser usar PERCENTILE_COUNT, pode fazer desta forma:

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

    Você pode então juntar isso de volta à sua consulta principal em BedroomsAvailablee UnitTypeId.

    Por exemplo:

    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

relate perguntas