sql - 我如何获得价格的中位数？

Question

在数据集中，每家商店都在销售一些书籍，每家商店对每本书都有自己的价格。在数据中，我有每本书的价格信息。通过 Amazon Athena 中的查询，我想计算特定时间段内每个商店和每种产品的中位数价格。

但老实说，我不知道该怎么做。到目前为止，这是我的查询:

SELECT product_id,
       shop_id,
       XXX AS median_price
FROM data_f
    WHERE site_id = 10
            AND year || month || day || hour >= '2020022500'
            AND year || month || day || hour < '2020022600'
GROUP BY product_id, shop_id

谢谢!

Answer 1

遗憾的是，AWS 不支持 median() 聚合函数或 percentile() 函数。也许最简单的方法是在子查询中使用 ntile(2)，然后取第一个图 block 的最大值(或第二个图 block 的最小值):

SELECT product_id, shop_id,
       MAX(CASE WHEN tile2 = 1 THEN price END) as median
FROM (SELECT d.*, NTILE(2) OVER (PARTITION BY product_id, shop_id ORDER BY price) as tile2
      FROM data_f d
      WHERE site_id = 10 AND
            action NOT IN ('base', 'delete') AND
            year || month || day || hour >= '2020022500' AND
            year || month || day || hour < '2020022600'
     ) d
GROUP BY product_id, shop_id;

注意:对于任何实际用途而言，这无疑已经足够好了。但是，“中位数”通常定义为当总行数为偶数时，中间两个值的平均值。如果你想学究气:

SELECT product_id, shop_id,
       (CASE WHEN COUNT(*) % 2 = 0
             THEN (MAX(CASE WHEN tile2 = 1 THEN price END) +
                   MIN(CASE WHEN tile2 = 2 THEN price END)
                  ) / 2.0
             ELSE MAX(CASE WHEN tile2 = 1 THEN price END)
        END) as median