如何在按clusterid分组过滤的SQL中新增issuedate为空的判断条件

需求规则 #

针对非空的同一clusterid分组执行过滤，规则如下：

• 若分组内issuedate列全部为空，则取关联inn数量最多的publid对应数据
• 若分组内issuedate不全相等，则取issuedate为最新日期的数据
• 若分组内issuedate全部相等，则取operdate为最新日期的数据
• 若分组内issuedate、operdate都全部相等，则取关联inn数量最多的publid对应数据

现有代码问题 #

已实现后三个条件的过滤逻辑，无法正确插入第一条规则的判断逻辑，尝试使用case when编写的代码语法错误。

原有可实现条件2/3/4的代码 #

SELECT m.* 
FROM (SELECT a.*, ROW_NUMBER() over (partition by clusterid, inn order by cnt desc) rn
   FROM (SELECT b.* ,COUNT(inn) OVER (PARTITION BY publid) cnt 
   FROM (SELECT c.*, RANK() OVER (PARTITION BY clusterid order by issuedate desc,operdate desc) rnk
       FROM table as c
       WHERE clusterid is not null) as b
WHERE b.rnk=1) as a
 ) as m 
WHERE m.rn=1 

错误的修改尝试代码 #

SELECT m.* 
FROM (SELECT a.*, ROW_NUMBER() over (partition by clusterid, inn order by cnt desc) rn
   FROM (SELECT b.* ,COUNT(inn) OVER (PARTITION BY publid) cnt 
       FROM (SELECT c.*, CASE WHEN issuedate='' then OVER (PARTITION BY clusterid)
       else RANK() OVER (PARTITION BY clusterid order by issuedate desc,operdate desc) 
       end rnk
           FROM table as c
           WHERE clusterid is not null) as b
WHERE b.rnk=1) as a
 ) as m 
WHERE m.rn=1 

示例数据 #

期望输出结果 #

正确实现方案 #

实现逻辑 #

1. 先通过窗口函数统计每个clusterid分组下非空issuedate的数量，值为0则代表该分组满足条件1的触发要求
2. 调整排序规则：触发条件1时优先按publid关联的inn数量倒序，否则沿用原有的日期排序规则，兼容原有2/3/4逻辑

完整实现代码 #

SELECT m.* 
FROM (
    SELECT a.*, ROW_NUMBER() OVER(PARTITION BY clusterid, inn ORDER BY cnt DESC) rn
    FROM (
        SELECT b.*, COUNT(inn) OVER(PARTITION BY publid) cnt 
        FROM (
            SELECT c.*,
                RANK() OVER(
                    PARTITION BY clusterid 
                    ORDER BY 
                        -- 分组内issuedate全为空时，优先取inn关联数最多的publid
                        CASE WHEN COUNT(CASE WHEN issuedate != '' THEN 1 END) OVER(PARTITION BY clusterid) = 0 
                             THEN COUNT(inn) OVER(PARTITION BY publid) END DESC,
                        issuedate DESC,
                        operdate DESC
                ) rnk
            FROM `table` AS c
            WHERE clusterid IS NOT NULL
        ) AS b
        WHERE b.rnk=1
    ) AS a
) AS m 
WHERE m.rn=1 

内容的提问来源于stack exchange，提问作者lisam