业务需求:
- 近两年按周统计每周每个城市的下单量
- 根据订单量统计每周下单量TOP20的城市
- 每周四作为一周的第一天
实现SQL如下:
SET DATEFIRST 3
select * from
(select *,
ROW_NUMBER() over(partition by 年份,weekCount order by orderscount desc) rowNum
from (select Year(dd.StartDate) as 年份,dd.weekCount,
pu_city_airline,sum(orderscount) orderscount
from Repl_DJ_PerDay_Summary(nolock) ssum
left join (SELECT DISTINCT
weekCount,
StartDate,
CONVERT( CHAR(10),
CASE
WHEN ff.weekCount = 53 THEN
DATEADD(DAY, 7, ff.StartDate)
ELSE
ff.EndDate
END,
120
) EndDate,
CONVERT(