Snowflake中基于视图动态计算USER_ID去重计数的方案咨询

我有一张结构和数据如下的表，目前可通过示例查询获取WHERE子句过滤条件下的USER_ID去重计数：

WITH CTE(REPORT,INSTANCE,ROLE,LOGIN_TIME,ISACTIVE,WEB_ACCESS,USER_ID) AS (
SELECT * FROM VALUES 
        ('R1','ABC','ADMIN','2024-04-05 16:00:00','1','YES','U1'),
        ('R1','ABC','DEV','2024-04-04 16:00:00','1','NO','U2'),
        ('R1','ABC','ADMIN','2024-04-03 16:00:00','1','NO','U3'),
        ('R1','ABC','ADMIN','2024-04-07 16:00:00','1','YES','U1'),
        ('R1','ABC','ADMIN','2024-04-09 16:00:00','1','YES','U1'),
        ('R1','ABC','DEV','2024-04-06 16:00:00','1','NO','U2')
)

-- 示例查询结果
-- SELECT COUNT( DISTINCT USER_ID) FROM CTE WHERE WEB_ACCESS='NO'; -- 返回2
-- SELECT COUNT( DISTINCT USER_ID) FROM CTE WHERE ROLE='ADMIN';-- 返回2
-- SELECT COUNT( DISTINCT USER_ID) FROM CTE WHERE ROLE='ADMIN' and WEB_ACCESS='YES';-- 返回1

实际需求是基于该表创建供终端用户使用的视图，当用户通过自定义WHERE过滤条件查询视图时，需将符合过滤条件的USER_ID去重计数作为单独列返回。但标准SQL的普通视图无法实现此功能，现咨询：是否可通过窗口函数、透视或其他方法实现该需求，且不使用UDTF？

假设基于上述临时表创建视图的DDL如下：

CREATE VIEW REPORT_VIEW AS
SELECT
    REPORT,INSTANCE,ROLE,LOGIN_TIME,ISACTIVE,WEB_ACCESS,USER_ID,'Use some logic to get distinct count on USER_ID dynamically' as DISTINCT_USER_ID_COUNT
FROM MY_TABLE

期望查询视图时，DISTINCT_USER_ID_COUNT列能根据过滤条件动态赋值：

SELECT * FROM REPORT_VIEW WHERE WEB_ACCESS='NO';-- 此查询中DISTINCT_USER_ID_COUNT应返回2

SELECT * FROM REPORT_VIEW WHERE WEB_ACCESS='YES' AND ROLE='ADMIN';-- 此查询中DISTINCT_USER_ID_COUNT应返回1

可以通过窗口函数实现这个需求，不需要UDTF或复杂透视逻辑，核心是利用窗口函数在过滤后的结果集上计算全局去重计数。

视图创建语句 #

直接在视图中使用COUNT(DISTINCT USER_ID) OVER ()，SQL引擎会先应用用户查询时的WHERE过滤条件，再在过滤后的结果集上计算去重用户数：

CREATE VIEW REPORT_VIEW AS
SELECT
    REPORT,
    INSTANCE,
    ROLE,
    LOGIN_TIME,
    ISACTIVE,
    WEB_ACCESS,
    USER_ID,
    COUNT(DISTINCT USER_ID) OVER () AS DISTINCT_USER_ID_COUNT
FROM MY_TABLE

验证效果 #

用测试数据验证：

1. 执行SELECT * FROM REPORT_VIEW WHERE WEB_ACCESS='NO';，返回的每一行中DISTINCT_USER_ID_COUNT均为2，对应符合条件的去重用户U2、U3。
2. 执行SELECT * FROM REPORT_VIEW WHERE WEB_ACCESS='YES' AND ROLE='ADMIN';，返回的每一行中DISTINCT_USER_ID_COUNT均为1，对应符合条件的唯一用户U1。

兼容低版本SQL引擎 #

如果你的SQL引擎不支持窗口函数中的COUNT(DISTINCT)（比如MySQL 8.0.22之前的版本），可以用DENSE_RANK()替代：通过对USER_ID生成密集排名，最大排名值即为去重用户数。对应的视图创建语句如下：

CREATE VIEW REPORT_VIEW AS
SELECT
    REPORT,
    INSTANCE,
    ROLE,
    LOGIN_TIME,
    ISACTIVE,
    WEB_ACCESS,
    USER_ID,
    MAX(user_rank) AS DISTINCT_USER_ID_COUNT
FROM (
    SELECT
        *,
        DENSE_RANK() OVER (ORDER BY USER_ID) AS user_rank
    FROM MY_TABLE
) t
GROUP BY REPORT,INSTANCE,ROLE,LOGIN_TIME,ISACTIVE,WEB_ACCESS,USER_ID

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