Vertica:如果没有找到记录则返回零

Question

我运行这个查询是为了获得过去3个月每个用户的平均登录次数。如果用户在最近3个月内登录过，则获取其平均值，否则返回0。

我尝试了许多不同的方法，但似乎如果用户在过去3个月内没有登录，就没有记录，count()不返回0。它什么也不返回。

1) select case count(*)
WHEN 0
THEN 0
ELSE count(creationTS) / 3
END as average
from table_name where creationTS >= add_months(now(), -3)
and userId = '110'
group by userId;
2) select COALESCE(count(creationTS)/3,0) as average
from table_name where creationTS >= add_months(now(), -3)
and userId = '110'
group by userId;

如果在条件' createts>= add_months(now()， -3)'中找到记录，则给出正确的结果，但不存在记录，则不返回任何结果。在这种情况下，我如何返回0呢?

Answer 1

试一下:

a)在全选择中从基表中获取所有不同的userid。

b)在用户id和登录日期不早于3个月的前提下，将full-select与基本表左连接

c)计算在基本表中找到的用户id，如果连接失败默认为NULL，使用NVL()在NULL的情况下强制为0，并按用户id

分组。

WITH
-- sample input data,not part of real query
indata(userid,login_dt) AS (
SELECT 'arthur',   DATE '2021-09-15'
UNION ALL SELECT 'arthur',   DATE '2021-08-27'
UNION ALL SELECT 'arthur',   DATE '2021-08-01'
UNION ALL SELECT 'trillian', DATE '2021-09-27'
UNION ALL SELECT 'trillian', DATE '2021-08-15'
UNION ALL SELECT 'trillian', DATE '2021-06-27'
UNION ALL SELECT 'ford',     DATE '2021-02-27'
UNION ALL SELECT 'ford',     DATE '2021-04-27'
)
,
userids AS (
SELECT DISTINCT
userid
FROM indata
)
SELECT
userids.userid
, NVL(COUNT(indata.userid),0) AS login_count
FROM userids
LEFT JOIN indata
ON userids.userid=indata.userid
AND login_dt >= ADD_MONTHS(CURRENT_DATE,-3)
GROUP BY
userids.userid
;
userid  | login_count 
----------+-------------
arthur   |           3
ford     |           0
trillian |           2