主页 / dba / 问题 / 313769
Accepted
Pantea
Asked: 2022-06-26 03:05:24 +0800 CST

查询以查找指定日期之间的活动天数（状态 = ON 的天数）

  • 772

我有一张桌子，如下所示：

create table z_test_duration
( Days     date,
  Status   char(8)
);

样本数据如下：

天 地位
2022 年 1 月 1 日 上
2022 年 1 月 2 日 上
2022 年 1 月 3 日 上
2022 年 1 月 4 日 离开
2022 年 1 月 5 日 上
2022 年 1 月 6 日 离开
2022 年 1 月 7 日 上
2022 年 1 月 8 日 上
2022 年 1 月 9 日 离开

想要的结果是这样的

时间到了 OFF_DATE COUNT_OF_ACTIVE_DAYS
2022 年 1 月 1 日 2022 年 1 月 4 日 3
2022 年 1 月 5 日 2022 年 1 月 6 日 1
2022 年 1 月 7 日 2022 年 1 月 9 日 2

到目前为止，我的解决方案是：

select min(days) on_date, 
       off_day off_date, 
       off_day - min(days) cnt
       
  from (select t1.off_day, 
               t1.prev_offday, 
               t2.days            
          from (                
                select t.days off_day,
                        nvl(lag(t.days, 1) over(order by t.days),convert(datetime, '1/1/2022') - 100) prev_offday
                  from z_test_duration t
                 where t.status = 'off'
                                 
                ) t1
         inner join z_test_duration t2
            on t2.days > t1.prev_offday
           and t2.days < t1.off_day)
 group by off_day;

我在想是否有更好的方法来解决这个问题，如果你能分享你解决这个问题的方法，我将不胜感激。

提前致谢。

sql-server sql-server-2016

1 个回答

  1. Best Answer

    这是一个“孤岛”问题。

    一种流行且有效的解决方案是按所需顺序对行进行编号。当序列中有间隙时，排序列和行号之间的差异也会跳跃。

    让我们一步一步来看看。一、编号：

    SELECT 
    Z.*, 
    Seq = Z.[Days], -- ordering column
    rn = ROW_NUMBER() OVER (ORDER BY Z.[Days]) -- numbering
FROM dbo.z_test_duration AS Z
WHERE Z.[Status] = 'on';
    天 地位 序列 rn
    2022-01-01 上 2022-01-01 1
    2022-01-02 上 2022-01-02 2
    2022-01-03 上 2022-01-03 3
    2022-01-05 上 2022-01-05 4
    2022-01-07 上 2022-01-07 5
    2022-01-08 上 2022-01-08 6

    请注意，Seq值以相同的速度增加，rn直到出现间隙。rn通过从Seq值中减去，我们可以更清楚地看到这一点。

    这里唯一稍微复杂的Seq是 a date，因此我们需要在减去之前将其转换为数字。我在DATEDIFF这里使用了这个函数，但是任何将日期转换为数字的一致方法都可以。

    SELECT 
    Z.*, 
    Seq = Z.[Days],
    diff = 
        DATEDIFF(DAY, '2022-01-01', Z.[Days]) - 
            ROW_NUMBER() OVER (
                ORDER BY Z.[Days]) 
FROM dbo.z_test_duration AS Z
WHERE Z.[Status] = 'on';
    天 地位 序列 差异
    2022-01-01 上 2022-01-01 -1
    2022-01-02 上 2022-01-02 -1
    2022-01-03 上 2022-01-03 -1
    2022-01-05 上 2022-01-05 0
    2022-01-07 上 2022-01-07 1
    2022-01-08 上 2022-01-08 1

    diff对于组中的每个连续元素，这些值都是相同的。

    现在我们知道如何分组了，最终查询直接如下：

    SELECT
    ON_DATE = MIN(G.Seq), 
    OFF_DATE = DATEADD(DAY, 1, MAX(G.Seq)),
    COUNT_OF_ACTIVE_DAYS = 1 + DATEDIFF(DAY, MIN(G.Seq), MAX(G.Seq))
FROM 
(
    SELECT 
        Z.*, 
        Seq = Z.[Days],
        grp = 
            DATEDIFF(DAY, '2022-01-01', Z.[Days]) - 
                ROW_NUMBER() OVER (
                    ORDER BY Z.[Days]) 
    FROM dbo.z_test_duration AS Z
    WHERE Z.[Status] = 'on'
) AS G
GROUP BY G.grp;
    时间到了 OFF_DATE COUNT_OF_ACTIVE_DAYS
    2022-01-01 2022-01-04 3
    2022-01-05 2022-01-06 1
    2022-01-07 2022-01-09 2

    db<>fiddle在线演示

    • 4

相关问题