sql - 向密集秩函数添加条件子句(Where)

Question

我想创建一个排名函数来计算一个人访问属性 BY DATE 的次数，但条件是不包括访问类别。 '调用'

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

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

表格

---- ActivityID ------------Property --------DATE ------CommunicationType ---------------- -等级

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

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

Answer 1

您需要按Property 和CommunicationType 进行分区:

表:

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')

声明:

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

结果:

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