Sql Sever按7天分组统计次数和金额

Question

目的：一周内开单超过2次（上一个提问未能完全解决问题）
表名：tbs
字段如图：（该电脑没数据库，只能用excel将就，请见谅）

数据如下：

本人思路：
一、循环判断同身份证号码下，2个开单时间在7天内，即可视为同一周；
二、对“一”的结果按同一周分组，统计次数。

第一步：

SELECT * FROM
(
select a.*,ROW_NUMBER() over (PARTITION BY a.身份证号码 ORDER BY a.开单日期) as a_n,
ROW_NUMBER() over (PARTITION BY b.身份证号码 ORDER BY b.开单日期) as b_n  
from tbs a
INNER JOIN 
tbs b
ON a.身份证号码=b.身份证号码 
AND ABS( DATEDIFF(DAY,CONVERT(VARCHAR(100),a.开单日期),CONVERT(VARCHAR(100),b.开单日期)) )<7  ) kk
WHERE kk.a_n<>kk,b.n;

第二步：不会了。。想过用rank+partition之类的分组，怎么也没办法得到同一值再分组，实在想不到了。

理想结果：

请各位同学踊跃发言，积极讨论啦~~。在下想很久了。

Answer 1

1、使用窗口函数吧：

with a as (
        select 'aa' 姓名, '111' 身份证号码, convert( datetime,'2022-01-01 10:00:00' ) 开单日期, 10 金额 union all
        select 'aa' 姓名, '111' 身份证号码, convert( datetime,'2022-01-04 10:00:00' ) 开单日期, 20 金额 union all
        select 'aa' 姓名, '111' 身份证号码, convert( datetime,'2022-01-08 10:00:00' ) 开单日期, 30 金额 union all
        select 'aa' 姓名, '111' 身份证号码, convert( datetime,'2022-01-10 10:00:00' ) 开单日期, 40 金额 union all
        select 'bb' 姓名, '222' 身份证号码, convert( datetime,'2022-02-11 11:00:00' ) 开单日期, 15 金额 union all
        select 'bb' 姓名, '222' 身份证号码, convert( datetime,'2022-02-14 11:00:00' ) 开单日期, 25 金额 union all
        select 'bb' 姓名, '222' 身份证号码, convert( datetime,'2022-02-14 16:00:00' ) 开单日期, 35 金额 union all
        select 'cc' 姓名, '333' 身份证号码, convert( datetime,'2022-03-01 13:00:00' ) 开单日期, 50 金额 union all
        select 'cc' 姓名, '333' 身份证号码, convert( datetime,'2022-03-04 13:00:00' ) 开单日期, 60 金额 union all
        select 'cc' 姓名, '333' 身份证号码, convert( datetime,'2022-03-08 13:00:00' ) 开单日期, 70 金额 union all
        select 'cc' 姓名, '333' 身份证号码, convert( datetime,'2022-03-13 13:00:00' ) 开单日期, 80 金额 )
    , b as (
        select *
             , datediff( day, 开单日期, lag( 开单日期, 2 ) over( partition by 身份证号码 order by 开单日期 ) ) 间隔前两条天数
             , datediff( day, 开单日期, lag( 开单日期 ) over( partition by 身份证号码 order by 开单日期 ) ) 间隔前一条天数
             , datediff( day, 开单日期, lead( 开单日期 ) over( partition by 身份证号码 order by 开单日期 ) ) 间隔后一条天数
             , datediff( day, 开单日期, lead( 开单日期, 2 ) over( partition by 身份证号码 order by 开单日期 ) ) 间隔后两条天数
          from a )
select 姓名, 身份证号码, 开单日期, 金额 from b where 间隔前两条天数 > -7 or 间隔后一条天数 - 间隔前一条天数 < 7 or 间隔后两条天数 < 7

构建数据：

执行结果：