时间戳

首页 > 解决方案 > 如何通过固定一些变量来计算中位数？

sql - 如何通过固定一些变量来计算中位数？

问题描述

我有一个已经汇总的数据集。这基本上显示了每天每只猫、root_cat 和集群的中位数价格。

date         cluster  root_cat cat median_price
2020-12-07    A          X     1      20
2020-12-07    A          X     2      15
2020-12-07    A          X     2      30
2020-12-08    B          Y     3      24

这是我为计算中位数价格而编写的查询。

SELECT date,
         page_impressions_cluster,
         root_cat,
         cat,
         MAX(CASE
        WHEN tile2 = 1 THEN
        min_price/100 END) AS median
FROM 
    (SELECT pl.*,
         NTILE(2)
        OVER (PARTITION BY product_id
    ORDER BY  min_price) AS tile2
    FROM pl
    WHERE cluster is NOT null
          AND (date_parse(date, '%Y-%m-%d') >= current_date - interval '15' day) ) d
GROUP BY  1, 2, 3, 4

现在，我想再增加一列，显示除最近一天之外的每只猫和 root_cat 过去 14 天的中位数价格。我怎样才能做到这一点？

这是所需的输出：

date         cluster  root_cat cat median_price  median_price_root     median_price_cat
2020-12-07    A          X     1      20                20                    20
2020-12-07    A          X     2      15                20                    22,5
2020-12-07    A          X     2      30                20                    22,5
2020-12-08    B          Y     3      24                24                    24

标签： sqlamazon-athena

解决方案

如果中位数的近似值足够好，那么您可以使用

SELECT date,
         page_impressions_cluster,
         root_cat,
         cat,
         MAX(CASE
        WHEN tile2 = 1 THEN
        min_price/100 END) AS median,
        approx_percentile(price, 0.5) -- <<== the 0.5 percentile is the median
FROM ...

请参阅此处approc_percentile的功能文档。

推荐阅读