用于月数的嵌套SQL查询

Question

我是SQL的新手，想知道如何为这个问题编写查询。假设我们有以下字段: date_created date_unsubscribed subscriberid

​

​

如何编写SQL查询，按月列出订阅列表、取消订阅列表的人数，以及有多少净订阅者(新订阅者减去取消订阅者)。所有这些都在一个查询中...

Answer 1

以下是使用conditional aggregation和union all的一种选择

select month(dt), 
       count(case when subscribe = 1 then 1 end) subscribecount,
       count(case when subscribe = -1 then 1 end) unsubscribecountt,
       sum(subscribe) overallcount
from (
   select date_created as dt, 1 as subscribe
   from yourtable
   union all
   select date_unsubscribed, -1
   from yourtable
   where date_unsubscribed is not null
) t
group by month(dt) 

子查询创建一个带有subscribe或unsubscribe标志的日期列表。然后，您可以结合使用count和case来确定适当的订阅者/取消订阅者数量。

SQL Fiddle Demo

Answer 2

您可以编写一个sum(case) (带条件的sum )进行聚合-假设date_created列从不为空。例如：

ORACLE：

SELECT
TO_CHAR(DATE_CREATED,'MM-YYYY') CREATE_MONTH
,SUM(CASE WHEN date_unsubscribed is not null then 1 else 0 end) unsubscribed
,SUM(CASE WHEN date_unsubscribed is null then 1 else 0 end) subscribed
,COUNT(SUBSCRIBER_ID)
FROM
--YOURTABLENAME
--WHERE
--WHATEVER OTHER CONDITIONS YOU HAVE APPLY
GROUP BY TO_CHAR(DATE_CREATED,'MM-YYYY')

MYSQL：

SELECT
    DATE_FORMAT(DATE_CREATED,'%m-%Y') CREATE_MONTH
    ,SUM(CASE WHEN date_unsubscribed is not null then 1 else 0 end) unsubscribed
    ,SUM(CASE WHEN date_unsubscribed is null then 1 else 0 end) subscribed
    ,COUNT(SUBSCRIBER_ID)
    FROM
    --YOURTABLENAME
    --WHERE
    --WHATEVER OTHER CONDITIONS YOU HAVE APPLY
    GROUP BY DATE_FORMAT(DATE_CREATED,'%m-%Y')

Answer 3

Oracle解决方案

下面是一个使用PIVOT运算符的查询，该运算符正是为这类工作创建的，并汇总以获得净值。这只是为了说明；我假设年份是用户或应用程序的输入(绑定变量:year，输出设置为2015 )，并且我显示了1月到6月的摘要。

with
     test_data ( date_created, date_unsubscribed, subscriber_id ) as (
       select date '2015-05-10', null             , 330053448 from dual union all
       select date '2015-04-28', null             , 330053457 from dual union all
       select date '2015-05-10', null             , 330053466 from dual union all
       select date '2015-04-28', null             , 220053475 from dual union all
       select date '2015-04-28', date '2015-05-10', 330053484 from dual
     ),
     prep ( type, val, mth ) as (
       select  'Subscribed'  ,  1, extract(month from date_created)      from test_data
          where extract(year from date_created)      = :year
       union all
       select  'Unsubscribed', -1, extract(month from date_unsubscribed) from test_data
          where extract(year from date_unsubscribed) = :year
     ) 
select nvl(type, 'Net Subscr') as description,
       nvl(sum(jan), 0) as jan, nvl(sum(feb), 0) as feb, nvl(sum(mar), 0) as mar, 
       nvl(sum(apr), 0) as apr, nvl(sum(may), 0) as may, nvl(sum(jun), 0) as jun
from prep
pivot (
         sum(val)
         for mth in (1 as jan, 2 as feb, 3 as mar, 4 as apr, 5 as may, 6 as jun)
      )
group by rollup(type)
order by case type when 'Subscribed' then 1 when 'Unsubscribed' then 2 else 3 end
;


DESCRIPTION         JAN        FEB        MAR        APR        MAY        JUN
------------ ---------- ---------- ---------- ---------- ---------- ----------
Subscribed            0          0          0          3          2          0
Unsubscribed          0          0          0          0         -1          0
Net Subscr            0          0          0          3          1          0

3 rows selected.