sql - SQL将TY和LY拉到同一行的列中

Question

我对 SQL 不是很好，但我们开始吧。我一直在尝试按周提取 sku 计数TY并将LY它们放在单独的列中，但在同一行中，具体取决于其他变量......即..

          Week    TY         LY
ABC        6       30         50
ABC        5      100         20
ABC        4       25         25

Select
    A.Store,
    B.Week_Num
    COUNT(A.ABC) AS TY,
    COUNT(A.ABC) AS LY
FROM [Table] O360
join Date D on D.Week_num = O360.Week_num

代码在上面进行了简化。挑战在于，我试图呼吁过去的 3 个星期六和每个星期六前 371 天代表LY......任何帮助都会令人惊叹。

标签： sqldatedynamicsubquery

Answer 1

我怀疑你想要lag(). 假设您每周都有数据，那么：

SELECT t.Store, d.Week_Num,
       COUNT(*) AS TY,
       LAG(COUNT(*), 53) OVER (PARTITION BY t.Store, d.Week_num) AS LY
FROM [Table] t JOIN
     Date D 
     ON D.Week_num = t.Week_num
GROUP BY t.Store, d.Week_Num;