无法计算中位数-SQL Server 2017

我正在尝试计算每个类别中交易的中位数。 一些注意事项(因为下面的数据集是更大的数据集的一小段):

  • 员工可以属于多个类别
  • 每笔交易的中位数应> 0
  • 并非每个人都出现在每个类别中

数据设置如下:

|  Person | Category | Transaction |
|:-------:|:--------:|:-----------:|
| PersonA |   Sales  |      27     |
| PersonB |   Sales  |      75     |
| PersonC |   Sales  |      87     |
| PersonD |   Sales  |      36     |
| PersonE |   Sales  |      70     |
| PersonB |   Buys   |      60     |
| PersonC |   Buys   |      92     |
| PersonD |   Buys   |      39     |
| PersonA |    HR    |      59     |
| PersonB |    HR    |      53     |
| PersonC |    HR    |      98     |
| PersonD |    HR    |      54     |
| PersonE |    HR    |      70     |
| PersonA |   Other  |      46     |
| PersonC |   Other  |      66     |
| PersonD |   Other  |      76     |
| PersonB |   Other  |      2      |

理想的输出如下所示:

| Category | Median | Average |
|:--------:|:------:|:-------:|
|   Sales  |   70   |    59   |
|   Buys   |   60   |    64   |
|    HR    |   59   |    67   |
|   Other  |   56   |    48   |

我可以通过以下方式获得平均值:

SELECT
    Category,
    AVG(Transaction) AS Average_Transactions
FROM
    table
GROUP BY
    Category

而且效果很好!

This post tried to help me find the median. What I wrote was:

SELECT
    Category,
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY Transaction) OVER (PARTITION BY Category) AS Median_Transactions
FROM
    table
GROUP BY
    Category

但我得到一个错误:

Msg 8120: Column 'Transactions' is invalid in the select list because it is not contained in either an aggregate function or the **GROUP BY** clause

我怎样才能解决这个问题?

评论
  • ret
    ret 回复

    这不是最佳选择,但这是您的解决方案

    SELECT DISTINCT
           category,
           PERCENTILE_DISC(0.5)WITHIN GROUP(ORDER BY val) OVER (PARTITION BY category) AS Median_Transactions,
           AVG(val) OVER (PARTITION BY d.category)  [AVG]
    FROM #data d;
    
  • 间接性抑郁
    间接性抑郁 回复
  • 奥特蛋o
    奥特蛋o 回复

    You can do what you want using SELECT DISTINCT:

    SELECT DISTINCT Category,
           PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY Transaction) OVER (PARTITION BY Category) AS Median_Transactions
    FROM table;
    

    Unfortunately, SQL Server doesn't offer the PERCENTILE_ functions as window functions and doesn't have a MEDIAN() aggregation function. You can also do this using subqueries and counts.