时间戳

首页 > 解决方案 > SQL 计算自当月第一天以来每天的累积 Distinct 计数

sql - SQL 计算自当月第一天以来每天的累积 Distinct 计数

问题描述

我有一张表，其中包含 Date、Segment、Area、Province 和 Billing_nbr 行。我还想要一个基于该行的日期不同的计数Billing_nbr where total_revenue > 0，该日期可以追溯到当月的第一天（即本月至今计数）。这意味着它应该是从当月第一天到观察日期（即每行的 ddate）的所有 billing_nbrs 的累积计数。这意味着如果 billing_nbr 发生在第一天，它也应该包含在第二天的计数中，即使它没有发生在第二天，但如果它在两天都出现，则应该计算一次。Billing_nbr where total_revenue > 0以及按其他列分组的另一个正常计数。以下是我拥有的数据：

我有下面的加入，但它在这两个方面给了我相似的数字：

SELECT 
    MAIN_TABLE.*,
    TOT_SUBS_COUNT.N AS A1_SUBSCRIBERS_TOTAL,
    TOT_SUBS_COUNT_MTD.N AS TOTAL_MTD_A1_SUBSCRIBERS
FROM (
        select  
                ddate,
                SUM(TOTAL_REVENUE) AS REVENUE_TOTAL, 
                segment,
                province,
                area
        from CADA_PERMSISDN_DASHBOARD 
        GROUP BY province, area, segment, ddate
        order by ddate
) MAIN_TABLE
JOIN(
    select DDATE, count(DISTINCT BILLING_NBR) AS N, province, area, SEGMENT from CADA_PERMSISDN_DASHBOARD
    WHERE TOTAL_REVENUE > 0
    GROUP BY province, area, segment, ddate
    ORDER BY DDATE
) TOT_SUBS_COUNT ON MAIN_TABLE.DDATE = TOT_SUBS_COUNT.DDATE 
AND MAIN_TABLE.SEGMENT = TOT_SUBS_COUNT.SEGMENT 
AND MAIN_TABLE.PROVINCE = TOT_SUBS_COUNT.PROVINCE 
AND MAIN_TABLE.AREA = TOT_SUBS_COUNT.AREA
JOIN(
    select DDATE, count(DISTINCT BILLING_NBR) AS N, province, area, SEGMENT from CADA_PERMSISDN_DASHBOARD
    WHERE TOTAL_REVENUE > 0
    AND DDATE BETWEEN trunc((DDATE),'month') AND DDATE
    GROUP BY province, area, segment, ddate
    ORDER BY DDATE
) TOT_SUBS_COUNT_MTD ON MAIN_TABLE.DDATE = TOT_SUBS_COUNT_MTD.DDATE 
AND MAIN_TABLE.SEGMENT = TOT_SUBS_COUNT_MTD.SEGMENT
AND MAIN_TABLE.PROVINCE = TOT_SUBS_COUNT_MTD.PROVINCE 
AND MAIN_TABLE.AREA = TOT_SUBS_COUNT_MTD.AREA

第一个连接用于分组计数，第二个连接用于从当月第一天到观察日期（即每行的日期）的累积计数，并且它还必须按其他列分组. 计数列分别别名为 A1_SUBSCRIBERS_TOTAL 和 OTAL_MTD_A1_SUBSCRIBERS。下面是我得到的数据，你看不到我在两列上有相同的计数：

标签： sqloracle

解决方案

SELECT
TO_CHAR(A.ddate, 'YYYY-MM'), 
A.segment, 
A.province, 
A.area,
SUM(CASE WHEN total_revenue > 0 THEN 1 ELSE 0 END) TOTAL_GT_ZERO,
COUNT(*) TOTAL_SUBSCRIBERS
from CADA_PERMSISDN_DASHBOARD A
group by TO_CHAR(A.ddate, 'YYYY-MM'), A.segment, A.province, A.area

据我了解。但是你能提供你所期望的吗？

推荐阅读