时间戳

首页 > 解决方案 > 向密集秩函数添加条件子句（Where）

sql - 向密集秩函数添加条件子句（Where）

问题描述

我想创建一个 Rank 函数来计算一个人访问财产的次​​数，BY DATE但条件是不包括访问类别。'Calls' 

DENSE_RANK() over(partition by activitytable.[Property] 
ORDER BY activitytable.[Date] as Job rank

这样做会对我不想要的整个通讯表进行排名。

桌子

---- ActivityID ----------Property --------DATE ------CommunicationType ----------------Rank

1046        Red Property      30/10/2019           Field                  2                      
10467       Red Property      29/10/2019           Field                  1
10591       Red Property      28/10/2019          Calls                   
10971       Blue Property     27/10/2019           Field                  2
10971       Blue Property     26/10/2019           Field                  1
10971       Blue Property     26/10/2019           calls                     
10965       Green Property    24/10/2019           calls
10765       Green Property    23/10/2019           calls
10765       Green Property    19/10/2019           field                  3
10765       Green Property    15/10/2019           field                  2
10765       Green Property    12/10/2019           field                  1

理想情况下，我希望表格看起来像上面一样，以忽略通信类型列的调用元素并仅计算字段类别。我怎么能这样做？

标签： sqlsql-serverdense-rankranking-functions

解决方案

You need to partition by Property and CommunicationType:

Table:

CREATE TABLE #Data (
    ActivityID int,
    Property varchar(100),
    [DATE] date,
    CommunicationType varchar(10)
)
INSERT INTO #Data
    (ActivityID, Property, [DATE], CommunicationType)
VALUES
    (1046,  'Red Property',    '20191030', 'field'),
    (10467, 'Red Property',    '20191029', 'field'),
    (10591, 'Red Property',    '20191028', 'calls'),
    (10971, 'Blue Property',   '20191027', 'field'),
    (10971, 'Blue Property',   '20191026', 'field'),
    (10971, 'Blue Property',   '20191026', 'calls'),
    (10965, 'Green Property',  '20191024', 'calls'),
    (10765, 'Green Property',  '20191023', 'calls'),
    (10765, 'Green Property',  '20191019', 'field'),
    (10765, 'Green Property',  '20191015', 'field'),
    (10765, 'Green Property',  '20191012', 'field')

Statement:

SELECT 
    *,
    CASE 
        WHEN CommunicationType = 'field' THEN DENSE_RANK() OVER (PARTITION BY Property, CommunicationType ORDER BY [DATE] ASC)
        ELSE NULL
    END AS Rank
FROM #Data

Result:

ActivityID  Property    DATE        CommunicationType   Rank
10971   Blue Property   2019-10-26  calls               NULL
10971   Blue Property   2019-10-26  field               1
10971   Blue Property   2019-10-27  field               2
10765   Green Property  2019-10-23  calls               NULL
10965   Green Property  2019-10-24  calls               NULL
10765   Green Property  2019-10-12  field               1
10765   Green Property  2019-10-15  field               2
10765   Green Property  2019-10-19  field               3
10591   Red Property    2019-10-28  calls               NULL
10467   Red Property    2019-10-29  field               1
1046    Red Property    2019-10-30  field               2

推荐阅读