Teradata每天获取MTD唯一用户(例如，在3月12日，我需要拥有第1到第12个唯一用户.过去一年的每个日期也是如此

Question

我需要获得过去一年中每天的MTD唯一用户数。例如，如果我的数据像

dt  customername
1   a
1   b
2   a
2   c
3   b
3   a
4   c
4   d
4   e

预期输出-1日只有2个唯一用户。在2号(1&2号(3个用户上。在第3个(第1到第3个(3个用户。在第4天(第1到第4天(有5个用户。我需要这个在过去一年的每个月的每个日期

dt uniquecustcount
1  2
2  3
3  3
4  5

我的数据是下面的格式，bu核心逻辑是上面的MTD。感谢提供的任何帮助

CREATE MULTISET TABLE GK_DAILY_USERS ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
EVENT_DATE DATE FORMAT 'yyyy-mm-dd',
Subs_Id INTEGER,
PAYMENT_METHOD_CD VARCHAR(8) CHARACTER SET UNICODE NOT CASESPECIFIC,
Data_User_Flag VARCHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC,
BUS_UNIT VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
Event_Count INTEGER)
PRIMARY INDEX ( EVENT_DATE ,Subs_Id );
Insert into GK_DAILY_USERS values (?,?,?,?,?,?);

我正在尝试以下sql。我知道这个sql没有意义，因此正在寻找解决方案。

SELECT EVENT_DATE, MAX(R2) FROM (
SELECT EVENT_DATE 
,COUNT(A.SUBS_ID)OVER(ORDER BY ROWNUM,A.SUBS_ID) AS R2
FROM (
SELECT A.EVENT_DATE,A.SUBS_ID
,Row_Number() Over (Order by EVENT_DATE, SUBS_ID) rownum
,COUNT(A.SUBS_ID)OVER(PARTITION BY A.SUBS_ID ORDER BY A.EVENT_DATE) AS RUN_TOTAL3
FROM  GK_DAILY_USERS A 
WHERE Data_User_Flag='Y' 
AND subs_id in (566875703,289813839, 151153086,279262050,18378517)
ORDER BY EVENT_DATE, SUBS_ID
) A 
WHERE RUN_TOTAL3 = 1
) A GROUP BY EVENT_DATE ORDER BY 1
;

Answer 1

SELECT EVENT_DATE 
-- new customers per date
,Sum(SUBS_ID) AS daily_new_cust
-- running total of new customers per date
,Sum(Sum(SUBS_ID))
Over(ORDER BY EVENT_DATE
ROWS Unbounded Preceding) AS unique_cust
FROM
(
SELECT *
FROM  GK_DAILY_USERS
WHERE Data_User_Flag='Y' 
AND subs_id IN (566875703,289813839, 151153086,279262050,18378517)
QUALIFY
-- return a single row with the earliest date for each subs_id
Row_Number()
Over (PARTITION BY A.SUBS_ID
ORDER BY EVENT_DATE) = 1
) AS dt
GROUP BY EVENT_DATE 
ORDER BY 1
;