主页 / dba / 问题 / 191005
Accepted
GWR
Asked: 2017-11-16 09:39:02 +0800 CST

对有序集中具有空值的行子集进行分组

  • 772

假设我们有一个表，其中每一行都是一天，并且按这一天列排序。然后我们加入了一个成员数据集，显示成员在哪一天活跃（和不活跃）。

比方说我们当前的数据集看起来像这样……会员资格从第 3-5 天开始活跃，从第 5-8 天开始不活跃，从第 9 天开始活跃，等等。

DAY     DATE        MEMBER  ACTIVE
 1      2017-01-01  123     null
 2      2017-01-02  123     null
 3      2017-01-03  123     2017-01-03
 4      2017-01-04  123     2017-01-04
 5      2017-01-05  123     2017-01-05
 6      2017-01-06  123     null
 7      2017-01-07  123     null
 8      2017-01-08  123     null
 9      2017-01-09  123     2017-01-09
10      2017-01-10  123     2017-01-10

...所以ACTIVE=null意味着会员资格在那些日子里不活跃。

有了这个数据结构，我想得到一个“折叠”集，显示不活动/活动时间的“跨度”：

MEMBER  MIN(DATE)   MAX(DATE)   STATUS
123,    2017-01-01, 2017-01-02  INACTIVE
123,    2017-01-03, 2017-01-05  ACTIVE
123,    2017-01-06, 2017-01-08  INACTIVE
123,    2017-01-09, 2017-01-10  ACTIVE

我曾尝试使用 row_number() 以某种方式划分出特定状态的子集，但在这种情况下，在 ACTIVE 为空的行上使用min()/max()将它们视为一个组，而实际上，有几个不同的跨度“非活跃会员”。

为了分组目的，我如何区分非活跃成员的跨度？我可以使用什么技术来实现上面的输出？

这是生成虚拟源数据的脚本：

CREATE TABLE ##SRC (ID INT, D DATE, MEMBER INT, ACTIVE DATE);

INSERT INTO ##SRC (ID, D, MEMBER, ACTIVE)
SELECT 1, '2017-01-01', 123, NULL UNION 
SELECT 2, '2017-01-02', 123, NULL UNION 
SELECT 3, '2017-01-03', 123, '2017-01-03' UNION 
SELECT 4, '2017-01-04', 123, '2017-01-04' UNION 
SELECT 5, '2017-01-05', 123, '2017-01-05' UNION 
SELECT 6, '2017-01-06', 123, NULL UNION 
SELECT 7, '2017-01-07', 123, NULL UNION 
SELECT 8, '2017-01-08', 123, NULL UNION 
SELECT 9, '2017-01-09', 123, '2017-01-09' UNION 
SELECT 10, '2017-01-10',    123, '2017-01-10' 
;
sql-server sql-server-2008-r2

2 个回答

  1. Best Answer

    您的示例数据与您的描述不符，起初让我感到困惑。正如 sp_BlitzErik 指出的那样，这是一个孤岛和缺口问题。如果您有权访问窗口函数，则解决方案非常简单。首先，我们可以单独枚举每个成员的表，我们称其为 full_order（这恰好与 day 相同，但为了通用性我会添加它）。其次，我们可以枚举每个成员的表以及他们当天是否活跃，我们称之为 partial_order

     select day, active, date, member
      , row_number() over (partition by member 
                           order by day) as fullorder
      , row_number() over (partition by member
                          ,case when active is null then 0 else 1 end
                          order by day) as partialorder
 from src

DAY         ACTIVE     MEMBER      FULLORDER            PARTIALORDER        

----------- ---------- ----------- -------------------- --------------------
      1 -                  123                    1                    1
      2 -                  123                    2                    2
      3 01/03/2017         123                    3                    1
      4 01/04/2017         123                    4                    2
      5 01/05/2017         123                    5                    3
      6 -                  123                    6                    3
      7 -                  123                    7                    4
      8 -                  123                    8                    5
      9 01/09/2017         123                    9                    4
     10 01/10/2017         123                   10                    5

    如果 full_order 和 partial_order 之间的差异发生变化，则意味着 active 已从 null 变为值，反之亦然。因此，我们可以组成一个具有这种差异的组。在每个这样的组中，我们可以选择 min(active) 和 max(active) 来形成一个间隔：

    select member, grp, min(date), max(active) 
from (
    select day, active, date, member
         , row_number() over (partition by member order by day) 
         - row_number() over (partition by member
                             ,case when active is null then 0 else 1 end 
                              order by day) as grp  
    from src
) 
group by member, grp

MEMBER      GRP                  3          4         
----------- -------------------- ---------- ----------
    123                    0 01/01/2017 -         
    123                    2 01/03/2017 01/05/2017
    123                    3 01/05/2017 -         
    123                    5 01/08/2017 01/10/2017

    添加另一层嵌套以获得所需结果可能是最简单的方法：

    select member, min_active
     , coalesce(max_active, min_active) as max_active
     , case when max_active is null then 'INACTIVE' else 'ACTIVE' end as status 
from (
    select member, grp, min(date) as min_active, max(active) as max_active 
    from (
        select day, active, date, member
             , row_number() over (partition by member order by day) 
             - row_number() over (partition by member
                                 ,case when active is null then 0 else 1 end 
                                 order by day) as grp  
        from src
    ) 
    group by member, grp)

MEMBER      MIN_ACTIVE MAX_ACTIVE STATUS  
----------- ---------- ---------- --------
    123 01/01/2017 01/01/2017 INACTIVE
    123 01/03/2017 01/05/2017 ACTIVE  
    123 01/05/2017 01/05/2017 INACTIVE
    123 01/08/2017 01/10/2017 ACTIVE
    • 3

  2. 然后我们加入了一个成员数据集，显示成员在哪一天活跃（和不活跃）。

    对不起，如果理解错了。您应该发布两个表数据然后提及您的问题。这样可以保证获得最佳查询。

    我在我的查询中使用递归 CTE，如果您同时拥有这两个表，则可以避免这种情况。

    反正剧本很短

    CREATE TABLE #SRC (ID INT, D DATE, MEMBER INT, ACTIVE DATE);

INSERT INTO #SRC (ID, D, MEMBER, ACTIVE)
SELECT 1, '2017-01-01', 123, NULL UNION 
SELECT 2, '2017-01-02', 123, NULL UNION 
SELECT 3, '2017-01-03', 123, '2017-01-03' UNION 
SELECT 4, '2017-01-04', 123, '2017-01-04' UNION 
SELECT 5, '2017-01-05', 123, '2017-01-05' UNION 
SELECT 6, '2017-01-06', 123, NULL UNION 
SELECT 7, '2017-01-07', 123, NULL UNION 
SELECT 8, '2017-01-08', 123, NULL UNION 
SELECT 9, '2017-01-09', 123, '2017-01-09' UNION 
SELECT 10, '2017-01-10',    123, '2017-01-10' 

;with CTE as
(
select *, 1 flg  from #SRC where id=1

union ALL

select s.*
,case when s.active is null and c.active is null then  flg 
when s.active is not null and c.active is not null then  flg 
else flg+1 end   
from #SRC S 
inner JOIN cte c on s.member=c.member
and s.id=c.id +1
)
select member,flg,MIN(D) MinD ,max(D)MaxD
from cte
group by member,flg

drop table #SRC
    • 0

相关问题