如何移动列值到新创建的列?

Question

我有以下查询:

SELECT QueryRun, Count(*) as TotalN, Avg(MDays) as AvgMDays
FROM table_name 
WHERE YN = 1 AND HF = 0
GROUP BY QueryRun
ORDER BY QueryRun DESC;

返回以下输出:

QueryRun      TotalN        AvgMDays
4/4/23        100           10
3/30/23       115           7
3/23/23       90            8
3/16/23       85            6
3/9/23        105           11
3/2/23        95            12

我想编辑上面的查询，使其返回以下内容:

TotalN       Trailing4WkTotalN        AvgMDays         Trailing4WkAvgMDays
100          105                      10               11

我想返回一条记录:当前日期的TotalN值，4周前的TotalN值(在本例中为3/9/23)，当前日期的AvgMDays和4周前的AvgMDays。我试过使用铅，但它没有工作。

我怎样才能得到这个输出?

Answer 1

我的第一反应是使用APPLY横向连接。它应该可以工作，尽管我怀疑这是不是最有效的方法:

WITH grouped AS (
SELECT QueryRun, Count(*) as TotalN, Avg(MDays) as AvgMDays
FROM table_name 
WHERE YN = 1 AND HF = 0
GROUP BY QueryRun
)
SELECT TOP 1 g.QueryRun, g.TotalN, t.TotalN AS Trailing4WkTotalN, g.AvgMDays, t.AvgMDays As Trailing4WkAvgMDays
FROM grouped g
CROSS APPLY (
SELECT TOP 1 TotalN, AvgMDays
FROM grouped g0 
WHERE g0.QueryRun <= DATEADD(week, -4, t.QueryRun)
ORDER BY g0.QueryRun DESC
) t -- t is for "trailing"
ORDER BY g.QueryRun DESC

我希望你能更有效率，如果你能知道，绝对肯定的是，总有一天的记录是EXACTLY过去4周:

WITH cur AS (
SELECT TOP 1 QueryRun FROM table_Name ORDER BY QueryRun DESC
)
SELECT t.QueryRun, COUNT(*) AS TotalN, MAX(t0.TotalN) As Trailing4WkTotalN
Avg(MDays) As AvgMDays, MAX(t0.AvgMDays) As Trailing4WkAvgMDays
FROM table_name t
INNER JOIN cur ON cur.QueryRun = t.QueryRun
CROSS APPLY(
SELECT COUNT(*) As TotalN, Avg(MDays) As AvgMDays
FROM table_name t0
WHERE t0.QueryRun = DATEADD(week, -4, t.QueryRun)
) t0
GROUP BY t.QueryRun

请记住:任何日期不恰好相隔4周的可能性都会破坏这一点。

Answer 2

with data as (
SELECT QueryRun, Count(*) as TotalN, Avg(MDays) as AvgMDays,
lead(Count(*), 4) over (order by QueryRun desc) as Trailing4WkTotalDays,
lead(Avg(MDays), 4) over (order by QueryRun desc) as Trailing4WkAvgMDays,
row_number() over (order by QueryRun desc) as rn
FROM table_name 
WHERE YN = 1 AND HF = 0
-- possible optimization
and QueryRun >= datediff(week, -5, getdate())
GROUP BY QueryRun
)
select * from data where rn = 1;

也假定数据中没有空白

with data as (
SELECT QueryRun, Count(*) as TotalN, Avg(MDays) as AvgMDays,
max(QueryRun) over () as maxQR
FROM table_name 
WHERE YN = 1 AND HF = 0
GROUP BY QueryRun
)
select
maxQR as QueryRun,
min(case when QueryRun = maxQR then TotalN end) as TotalN,
min(case when QueryRun = maxQR then AvgMDays end) as AvgMDays,
min(case when QueryRun = dateadd(week, -4, maxQR) then TotalN end) as Trailing4WkTotalDays,
min(case when QueryRun = dateadd(week, -4, maxQR) then AvgMDays end) as Trailing4WkAvgMDays
from data;

这个没有假设现在有多少周。