Period over period SQL脚本？

Question

我有一个包含2列的数据集：“Date”和“Total Sales”。我的日期是2021年01月01日，2021年01月01日...以此类推，直到2022年12月01日。基本上，我想添加另一行，其中我有一个“上个月”列，它在与当前月份相同的行中给出了上个月的总销售额(否则为null) --例如，假设我的日期列01-01-2021和02-01-2021中有2行，总销售额将分别为10美元和20美元。如何创建显示以下内容的列：

Date       |Sales | Previous Month Sales|
---------------------------------------------
01-01-2021 | $10  | null
02-01-2021 | $20  | $10

以此类推；这是我的查询：

CASE 

WHEN `Date` > DATE_SUB(`Date`, INTERVAL 1 MONTH) 

THEN `Monthly Sales`

ELSE 'null'

END

提前感谢

Answer 1

嗯，Domo的后端是运行MySQL后端引擎的(我记得上次接触Domo2018的时候)

我认为这只是一个SQL问题，我想知道一个简单的窗口函数是否可以做到这一点。

select Date,
       Sales,
       max (case when *month* = *this month -1* then Sales else null end) over (order by 1) as "Previous Month Sales"
   from table

您只需要弄清楚如何根据Domo现在使用的SQL方言将日期分解为月份。

干杯

Answer 2

我认为domo支持类似MySQL的语言，所以你可以这样做：

    with cte as
    (
    select date, 
      date + interval 1 month as next_month, 
      sales
    from sales
    )
    select a.date, 
    a.sales as current_sales, 
    b.sales as prior_month_sales
    from sales a
    left join cte b
      on b.next_month = a.date
    order by a.date

Answer 3

我通过使用左外部连接将表连接到表本身来完成此操作。outer join允许您保留上个月的空值。您匹配日期，以便计算1列以显示上个月(我使用EOMONTH()这样做，以确保我总是获得上个月并计算年份，如果是1月份)。

    IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL
    DROP TABLE #TEMP

CREATE TABLE #TEMP(
    [Date]          DATE
    ,[Sales]        INT
)

INSERT INTO #TEMP([Date],[Sales])
VALUES ('2020-12-20',50)
    ,('2021-01-20',100)
    ,('2021-02-20',200)
    ,('2021-03-20',300)
    ,('2021-04-20',400)
    ,('2021-05-20',500)

SELECT #TEMP.[Date]
    ,#TEMP.Sales
    ,TEMPII.Date [PREV M]
    ,TEMPII.Sales [PREV M SALES]
FROM #TEMP
    LEFT OUTER JOIN #TEMP TEMPII
        ON YEAR(EOMONTH(#TEMP.[Date],-1))*100+MONTH(EOMONTH(#TEMP.[Date],-1)) = YEAR(TEMPII.[Date])*100+MONTH(TEMPII.[Date])
ORDER BY #TEMP.[Date]

输出：

​