代码之家 › 专栏 › 技术社区 › chintuyadavsara

从日期起每个身份证都会丢失几个月

greenplum

-1

chintuyadavsara · 技术社区 · 5 年前

我有一个表,其中包含,memberid和date。我想为每一个成员显示从日期起丢失的月份。

这是输入表和期望表。

我试图创建一个临时表来生成从最小月到最大月的序列,但是我没有加入这两个表。有人能帮我做这件事吗。

这是我到目前为止试过的代码。

提取日期并存储在新列中

create temp table temp_table as select *, date_part('month', premiumpadidate) from training.premium  distributed by(memberid);

生成序列

create temp table temp_table_series as 
(select * from generate_series(cast((select min(date_part) from temp_table group by (memberid)) as integer), cast((select max(date_part) from temp_table group by (memberid)) as integer) )
) 
distributed by (generate_series)

我不明白如何加入这两个表格,以获得每个员工失踪的月份。

请帮我做这个

1 回复 | 直到 5 年前

1

0

Filipe 5 年前

这很容易用一个函数来完成,但是您可以在一个选择中完成。这是我帮你做的。我用union模拟了你的数据,并添加了一个“102”成员:

select 101 as id, '2016-01-01'::date as paidDate union
select 101 as id, '2016-02-01'::date as paidDate union
select 101 as id, '2016-04-01'::date as paidDate union
select 101 as id, '2016-08-01'::date as paidDate union
select 101 as id, '2016-11-01'::date as paidDate union
select 102 as id, '2016-01-01'::date as paidDate union
select 102 as id, '2016-02-01'::date as paidDate union
select 102 as id, '2016-04-01'::date as paidDate union
select 102 as id, '2016-08-01'::date as paidDate union
select 102 as id, '2016-11-01'::date as paidDate

你应该把它当作你的桌子。

然后,在你的桌子上我做了12个“案例”,以便检查这个月。如果找到月份,则输入“0”,如果没有,则输入月份值,如下所示:

select t.id, extract(month from t.paidDate),
    case when extract(month from t.paidDate) = 1 then 0 else 1 end as m1,
    case when extract(month from t.paidDate) = 2 then 0 else 2 end as m2,
    case when extract(month from t.paidDate) = 3 then 0 else 3 end as m3,
    case when extract(month from t.paidDate) = 4 then 0 else 4 end as m4,
    case when extract(month from t.paidDate) = 5 then 0 else 5 end as m5,
    case when extract(month from t.paidDate) = 6 then 0 else 6 end as m6,
    case when extract(month from t.paidDate) = 7 then 0 else 7 end as m7,
    case when extract(month from t.paidDate) = 8 then 0 else 8 end as m8,
    case when extract(month from t.paidDate) = 9 then 0 else 9 end as m9,
    case when extract(month from t.paidDate) = 10 then 0 else 10 end as m10,
    case when extract(month from t.paidDate) = 11 then 0 else 11 end as m11,
    case when extract(month from t.paidDate) = 12 then 0 else 12 end as m12
from (
    select 101 as id, '2016-01-01'::date as paidDate union
    select 101 as id, '2016-02-01'::date as paidDate union
    select 101 as id, '2016-04-01'::date as paidDate union
    select 101 as id, '2016-08-01'::date as paidDate union
    select 101 as id, '2016-11-01'::date as paidDate union
    select 102 as id, '2016-01-01'::date as paidDate union
    select 102 as id, '2016-02-01'::date as paidDate union
    select 102 as id, '2016-04-01'::date as paidDate union
    select 102 as id, '2016-08-01'::date as paidDate union
    select 102 as id, '2016-11-01'::date as paidDate
) t

之后,我们需要丢弃数据中存在的月份。我是用一个按身份分组的方法来做的。这将丢弃存在的月份(因为我们将它们归零),并将维持不存在的月份。这样地:

select id, 
    min(m1) as m1, min(m2) as m2, min(m3) as m3, min(m4) as m4, min(m5) as m5, min(m6) as m6, 
    min(m7) as m7, min(m8) as m8, min(m9) as m9, min(m10) as m10, min(m11) as m11, min(m12) as m12
from 
(
    select t.id, extract(month from t.paidDate),
        case when extract(month from t.paidDate) = 1 then 0 else 1 end as m1,
        case when extract(month from t.paidDate) = 2 then 0 else 2 end as m2,
        case when extract(month from t.paidDate) = 3 then 0 else 3 end as m3,
        case when extract(month from t.paidDate) = 4 then 0 else 4 end as m4,
        case when extract(month from t.paidDate) = 5 then 0 else 5 end as m5,
        case when extract(month from t.paidDate) = 6 then 0 else 6 end as m6,
        case when extract(month from t.paidDate) = 7 then 0 else 7 end as m7,
        case when extract(month from t.paidDate) = 8 then 0 else 8 end as m8,
        case when extract(month from t.paidDate) = 9 then 0 else 9 end as m9,
        case when extract(month from t.paidDate) = 10 then 0 else 10 end as m10,
        case when extract(month from t.paidDate) = 11 then 0 else 11 end as m11,
        case when extract(month from t.paidDate) = 12 then 0 else 12 end as m12
    from 
    (
        select 101 as id, '2016-01-01'::date as paidDate union
        select 101 as id, '2016-02-01'::date as paidDate union
        select 101 as id, '2016-04-01'::date as paidDate union
        select 101 as id, '2016-08-01'::date as paidDate union
        select 101 as id, '2016-11-01'::date as paidDate union
        select 102 as id, '2016-01-01'::date as paidDate union
        select 102 as id, '2016-02-01'::date as paidDate union
        select 102 as id, '2016-04-01'::date as paidDate union
        select 102 as id, '2016-08-01'::date as paidDate union
        select 102 as id, '2016-11-01'::date as paidDate
    ) t
) t
group by t.id

你可以将它作为你的结果来执行你拥有的任何规则,但是让我们通过移除零来进一步清理它,如下所示:

select id,
    case when m1 = 0 then null else m1 end, case when m2 = 0 then null else m2 end,
    case when m3 = 0 then null else m3 end, case when m4 = 0 then null else m4 end,
    case when m5 = 0 then null else m5 end, case when m6 = 0 then null else m6 end,
    case when m7 = 0 then null else m7 end, case when m8 = 0 then null else m8 end,
    case when m9 = 0 then null else m9 end, case when m10 = 0 then null else m10 end,
    case when m11 = 0 then null else m11 end, case when m12 = 0 then null else m12 end
from 
(
    select id, 
        min(m1) as m1, min(m2) as m2, min(m3) as m3, min(m4) as m4, min(m5) as m5, min(m6) as m6, 
        min(m7) as m7, min(m8) as m8, min(m9) as m9, min(m10) as m10, min(m11) as m11, min(m12) as m12
    from 
    (
        select t.id, extract(month from t.paidDate),
            case when extract(month from t.paidDate) = 1 then 0 else 1 end as m1,
            case when extract(month from t.paidDate) = 2 then 0 else 2 end as m2,
            case when extract(month from t.paidDate) = 3 then 0 else 3 end as m3,
            case when extract(month from t.paidDate) = 4 then 0 else 4 end as m4,
            case when extract(month from t.paidDate) = 5 then 0 else 5 end as m5,
            case when extract(month from t.paidDate) = 6 then 0 else 6 end as m6,
            case when extract(month from t.paidDate) = 7 then 0 else 7 end as m7,
            case when extract(month from t.paidDate) = 8 then 0 else 8 end as m8,
            case when extract(month from t.paidDate) = 9 then 0 else 9 end as m9,
            case when extract(month from t.paidDate) = 10 then 0 else 10 end as m10,
            case when extract(month from t.paidDate) = 11 then 0 else 11 end as m11,
            case when extract(month from t.paidDate) = 12 then 0 else 12 end as m12
        from 
        (
            select 101 as id, '2016-01-01'::date as paidDate union
            select 101 as id, '2016-02-01'::date as paidDate union
            select 101 as id, '2016-04-01'::date as paidDate union
            select 101 as id, '2016-08-01'::date as paidDate union
            select 101 as id, '2016-11-01'::date as paidDate union
            select 102 as id, '2016-01-01'::date as paidDate union
            select 102 as id, '2016-02-01'::date as paidDate union
            select 102 as id, '2016-04-01'::date as paidDate union
            select 102 as id, '2016-08-01'::date as paidDate union
            select 102 as id, '2016-11-01'::date as paidDate
        ) t
    ) t
    group by 
    t.id
) t

就这样,希望能有点帮助。

当做