时间戳

首页 > 解决方案 > 用于计算 MTD 指标的窗口函数

sql - 用于计算 MTD 指标的窗口函数

问题描述

我正在尝试创建一个可以计算多个指标 MTD 的查询。因此，如果我想计算 12 月 12 日的收入，那么它会将 12 月 1 日到 12 月 12 日之间的所有天数相加。

如果我想比较两个月之间的收入增长，那么它将将该区间与 11 月 1 日到 11 月 12 日进行比较。

我一直在尝试使用窗口函数来做到这一点。

select ms.date, ms.market, 
sum(ms.rev) as "rev_this_month",
lag(sum(ms.rev)) over (partition by ms.market order by ms.date) as "last_month_rev",

(sum(ms.rev) - (cast((lag(sum(ms.rev)) over 
(partition by ms.market order by ms.date))as float)))/ 
cast((lag(sum(ms.rev)) over 
(partition by ms.market order by ms.date))as float) as "MTD_rev_growth"

From market_sales ms

where 
a.date between trunc(DATE_TRUNC('day', current_date)) - interval '30 day'
 and trunc(DATE_TRUNC('day', current_date))

我会期待这样的事情：

Date       | Market | Rev_this_month | Rev_last_month | MTD
2018-12-12     Aus         10                5                1.0
2018-12-12     SF          40               40                 0
2018-12-12     PA          10               20               -0.50

但相反，我得到

Date       | Market | Rev_this_month | Rev_last_month | MTD_Rev_growth
2018-12-12     Aus         10                5                1.0
2018-12-11     Aus         5                 2                 x
2018-12-10     Aus         2                 0                 x
...
2018-12-12     SF         40                 40                0
2018-12-11     SF         40                 20               1.0

等等。

它与我的窗口函数有关，还是我在 where 子句中过滤内容的方式？

将不胜感激任何帮助。

编辑：

抱歉回来晚了，去露营了。无论如何，这里是一些基于请求的示例数据：

Market | Date        | Rev
Aus      2018-01-03    55
SF       2018-01-04    33
SF       2018-01-20    04
PA       2018-01-21    60
BAL      2018-01-29    70
SF       2018-02-12    31

标签： sqlamazon-redshift

解决方案

根据您的描述和显示的预期结果，您只想将当前月份与上个月相同的天数进行比较...

WITH
  data_window AS
(
  SELECT
    *,
    CASE WHEN market_sales.date >= DATE_TRUNC('month', CURRENT_DATE) THEN 1 ELSE 0 END   AS is_this_month
  FROM
    market_sales
  WHERE
        market_sales.date >= DATEADD('month', -1, DATE_TRUNC('month', CURRENT_DATE))
    AND DATE_PART('day', market_sales.date) <= DATE_PART('day', CURRENT_DATE)
)
SELECT
  CURRENT_DATE   AS date,
  market,
  SUM(CASE WHEN is _this_month = 1 THEN rev ELSE 0 END)  AS rev_this_month,
  SUM(CASE WHEN is _this_month = 0 THEN rev ELSE 0 END)  AS rev_last_month,
  CAST(SUM(CASE WHEN is _this_month = 1 THEN rev ELSE 0 END) AS FLOAT)
  /
  SUM(CASE WHEN is _this_month = 0 THEN rev ELSE 0 END)
  - 1.0
                                                         AS mtd_rev_growth
FROM
  data_window
GROUP BY
  market

推荐阅读