我需要帮助编写子查询

Question

我有一个像这样的查询来创建日期系列：

Select month
    From
    (select to_char(created_date, 'Mon') as Month,
           created_date::date as start_day,
           (created_date::date + interval '1 month - 1 day ')::date as end_day
    from generate_series(date '2021-01-26', 
                         date '2022-04-26', interval '1 month') as g(created_date)) AS "thang"

表格如下所示：

月份
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct

现在我想计算 KYC< 中的 status /代码>表。

所以我尝试这个：

Select
    (Select month
    From
    (select to_char(created_date, 'Mon') as Month,
           created_date::date as start_day,
           (created_date::date + interval '1 month - 1 day ')::date as end_day
    from generate_series(date '2021-01-26', 
                         date '2022-04-26', interval '1 month') as g(created_date)) AS "thang"),
    count(*) filter (where status = 4) as "KYC_Success"
From kyc
group by 1

我希望结果是这样的：

Month |  KYC_Success
Jan   |      234
Feb   |      435
Mar   |      546
Apr   |      157

但是它说

错误：用作表达式的子查询返回多行

我应该在此查询中更改什么？

Answer 1

让我们假设表 KYC 有一个名为 created_date 的时间戳列和状态列，并且您想要计算每月的成功状态 - 即使为零一个月内的成功项目。

SELECT thang.month
    , count(CASE WHEN kyc.STATUS = 'success' THEN 1 END) AS successes
FROM (
    SELECT to_char(created_date, 'Mon') AS Month
        , created_date::DATE AS start_date
        , (created_date::DATE + interval '1 month - 1 day ')::DATE AS end_date
    FROM generate_series(DATE '2021-01-26', DATE '2022-04-26', interval '1 month') AS g(created_date)
    ) AS "thang"
LEFT JOIN kyc ON kyc.created_date>= thang.start_date
    AND kyc.created_date < thang.end_date 
GROUP BY thang.month;