grouping by week(按周统计数据)

订单周统计查询

最新推荐文章于 2024-09-19 18:25:15 发布

原创最新推荐文章于 2024-09-19 18:25:15 发布 · 227 阅读

0 ·

CC 4.0 BY-SA版权

sql 专栏收录该内容

21 篇文章

订阅专栏

本文介绍了一个SQL查询案例，用于按周统计订单数量。通过日期运算确定每周起始和结束时间，并计算各周期内的订单总数。

select orderdate-weeknum+1 as week_start,
orderdate-weeknum+7 as week_end,
count(*) as ordernum
from
(
select datepart(weekday,orderdate+@@datefirst-1) as weeknum,orderdate
from dbo.orders
) as d
group by orderdate-weeknum+1 ,orderdate-weeknum+7

Table 1-10: Weekly Counts of Orders (abbreviated)
Open table as spreadsheet
*week_start*	*week_end*	*numorders*
1996-07-01 00:00:00.000	1996-07-07 00:00:00.000	2
1996-07-08 00:00:00.000	1996-07-14 00:00:00.000	6
1996-07-15 00:00:00.000	1996-07-21 00:00:00.000	6
1996-07-22 00:00:00.000	1996-07-28 00:00:00.000	5
1996-07-29 00:00:00.000	1996-08-04 00:00:00.000	6
1996-08-05 00:00:00.000	1996-08-11 00:00:00.000	5
1996-08-12 00:00:00.000	1996-08-18 00:00:00.000	6
1996-08-19 00:00:00.000	1996-08-25 00:00:00.000	5
1996-08-26 00:00:00.000	1996-09-01 00:00:00.000	6
1996-09-02 00:00:00.000	1996-09-08 00:00:00.000	5