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

目的：一周内开单超过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之类的分组，怎么也没办法得到同一值再分组，实在想不到了。

理想结果：

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

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

构建数据：

执行结果：

那这里就有一些细节需要确认了，比如一个人，开了5单，分别是第一天，第七天，第八天，第13天，第14天，怎么划分

如果分别是第一天，第七天，第10天，第15天，第16天，怎么划分

引用chatGPT作答，您可以尝试使用以下SQL语句来实现您的需求：

SELECT 
    t.身份证号码, 
    DATEPART(WEEK, t.开单日期) AS 第几周,
    COUNT(*) AS 开单次数,
    SUM(t.开单金额) AS 开单金额
FROM 
    tbs t
WHERE 
    EXISTS (
        SELECT 
            1
        FROM 
            tbs
        WHERE 
            身份证号码 = t.身份证号码 AND 
            DATEDIFF(DAY, 开单日期, t.开单日期) BETWEEN 0 AND 6 AND 
            开单日期 <> t.开单日期
    )
GROUP BY 
    t.身份证号码, 
    DATEPART(WEEK, t.开单日期)
HAVING 
    COUNT(*) > 2

该SQL语句将数据按照身份证号码和开单日期所在的周进行分组，并统计每个组内的开单次数和开单金额。其中，EXISTS子查询用于判断同一身份证号码下，开单时间相差不超过7天的记录是否存在，从而筛选出符合条件的数据。最后，使用GROUP BY和HAVING子句来过滤出开单次数超过2次的数据。

希望以上SQL语句能够解决您的问题，如有疑问请随时提出。

不知道你这个问题是否已经解决, 如果还没有解决的话:

• 以下回答来自chatgpt:

  以下为解答：

  使用以下SQL语句可以查询在一个7天时间段内开单超过2次的客户数量和总金额：

  SELECT 身份证号, SUM(金额) AS 总金额, COUNT(*) AS 超过2次的次数
  FROM tbs
  WHERE 开单时间 BETWEEN DATEADD(day, -7, GETDATE()) AND GETDATE()
  GROUP BY 身份证号
  HAVING COUNT(*) > 2
  

  其中，DATEADD(day, -7, GETDATE()) 表示当前时间减去7天，GETDATE() 表示当前时间。

  在SQL Server中，可以使用分组函数SUM和COUNT来实现这个目标。SUM用于求总金额，COUNT用于计算客户在7天时间段内开单的次数。GROUP BY按照身份证号进行分组，HAVING根据开单次数来找出开单超过2次的客户。

  建议在计算开单次数时，使用HAVING而不是WHERE语句，因为WHERE语句会过滤掉不符合条件的行，而HAVING只是限制最终结果的返回。

  如果想在存储过程中使用以上SQL语句，可以创建一个带参数的存储过程来实现：

  CREATE PROCEDURE sp_GetCustomersWithMoreThan2OrdersIn7Days
      @StartDate DATETIME,
      @EndDate DATETIME
  AS
  BEGIN
      SET NOCOUNT ON;
  
      SELECT 身份证号, SUM(金额) AS 总金额, COUNT(*) AS 超过2次的次数
      FROM tbs
      WHERE 开单时间 BETWEEN @StartDate AND @EndDate
      GROUP BY 身份证号
      HAVING COUNT(*) > 2
  END
  

  在上面的存储过程中，CREATE PROCEDURE来创建一个存储过程，@StartDate和@EndDate是两个参数，用于传递开始和结束时间。在存储过程中，使用SELECT语句来查询开单超过2次的客户，将结果返回给调用者。

  存储过程的优点是可以重复使用，可以减少重复的代码，以及提高性能，因为存储过程可以预编译和缓存，执行速度更快。

  以上是本题的解答，希望能够帮助到您。

如果你已经解决了该问题, 非常希望你能够分享一下解决方案, 写成博客, 将相关链接放在评论区, 以帮助更多的人 ^-^

建议按照每周来进行分组，这样可以查询相应的字段进行分组合计

统计同一个人最大开单时间和最小开单时间之差小于等于6，就可以确认开单大于2次了

以下答案由GPT-3.5大模型与博主波罗歌共同编写：
你已经很接近正确的答案了！实际上你只需要在第一步的基础上再加一步，使用group by来统计每个身份证号码在一周内的开单次数即可。

以下是完整的SQL代码：

SELECT 身份证号码, COUNT(*) as 开单次数, SUM(金额) as 总金额
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
GROUP BY 身份证号码
HAVING COUNT(*) >= 2

代码解释：

1. 在第一步中，我们使用了窗口函数（ROW_NUMBER）来为每个身份证号码的每个开单记录标号，接着我们使用INNER JOIN自连接表格，通过身份证号码和开单日期来比较两条记录，只有在开单时间7天内的两条记录才会被连接。
2. 在第二步中，我们使用了GROUP BY语句按照身份证号码分组，并统计每个组内的开单次数和总金额。注意我们在WHERE子句中加了一个过滤条件：只有在第一步中产生“相邻”的两条记录才会被统计。最后我们使用HAVING子句来筛选开单次数大于等于2的记录。

希望这个解答能解决你的问题！
如果我的回答解决了您的问题，请采纳！