sql - 查看从当前日期回溯 30 天的中位数

Question

背景:我有一张像下面这样的表格，我试图在其中获得中值 value基于每个 created_date 的滚动 30 天回顾按每个分区 city .
问题是我缺少某些城市的日期所以 bound by rows preceding在这种情况下不起作用。
注:我有一个 date-spine我可以利用的表格，但不确定它在这里是否有帮助


创建日期
城市
值(value)


2018-08-30
夏洛特
374900

2018-08-31
夏洛特
272000

2018-09-10
夏洛特
1

2018-09-24
夏洛特
365000

2018-10-04
夏洛特
342000

2018-10-07
夏洛特
460000

2018-10-08
夏洛特
91000

2018-10-15
夏洛特
342000

2018-10-18
夏洛特
155000

2018-10-19
夏洛特
222000

...
...
...


预期输出:


创建日期
城市
值(value)
MOVING_MEDIAN_30_DAY


2018-08-30
夏洛特
374900
374900

2018-08-31
夏洛特
272000
323450

2018-09-10
夏洛特
1
272000

2018-09-24
夏洛特
365000
318500

2018-10-04
夏洛特
342000
342000

2018-10-07
夏洛特
460000
353500

2018-10-08
夏洛特
91000
342000

2018-10-15
夏洛特
342000
342000

2018-10-18
夏洛特
155000
342000

2018-10-19
夏洛特
222000
342000

...
...
...
...


问题:如何使用 sql/snowflake 获得预期的输出？
提前致谢!!

Answer 1

自雪花的MEDIAN窗口函数不支持滑动框架，我们必须采取另一种方法。我们可以使用雪花的MEDIAN聚合函数以及自连接，以模拟窗口函数。

 SELECT original.created_date
      , original.city
      , original.value
      , MEDIAN(window.value) AS rolling_30_day_median
   FROM cities AS original
   LEFT JOIN cities AS window
     ON original.city = window.city
    AND DATEDIFF(days, original.created_date, window.created_date) BETWEEN -30 AND 0
  GROUP BY 1, 2, 3
  ORDER BY 1
;

这会产生所需的输出。


创建日期
城市
值(value)
ROLLING_30_DAY_MEDIAN


2018-08-30
夏洛特
374,900
374,900

2018-08-31
夏洛特
272,000
323,450

2018-09-10
夏洛特
1
272,000

2018-09-24
夏洛特
365,000
318,500

2018-10-04
夏洛特
342,000
342,000

2018-10-07
夏洛特
460,000
353,500

2018-10-08
夏洛特
91,000
342,000

2018-10-15
夏洛特
342,000
342,000

2018-10-18
夏洛特
155,000
342,000

2018-10-19
夏洛特
222,000
342,000