array的返回捕捉对应数据

最新推荐文章于 2024-07-17 23:15:19 发布

静听山水

最新推荐文章于 2024-07-17 23:15:19 发布

阅读量126

点赞数

分类专栏： Clickhouse 文章标签： clickhouse Powered by 金山文档

本文链接：https://blog.csdn.net/qq_41081716/article/details/129394692

版权

Clickhouse 专栏收录该内容

34 篇文章 0 订阅

订阅专栏

简单举例：新开信用卡银行

建表：

create table if not exists test.test_ks
(
    s_id  String,
    __rid  String,
    jg   String,
    clrq String,
    sxed String,
    bgsj String,
    rk String
)
    engine = MergeTree ORDER BY __rid
        SETTINGS index_granularity = 8192;

数据插入：

INSERT INTO test.test_ks (s_id, __rid, jg, clrq, sxed, bgsj, rk) VALUES ('小明', '1111', '商业银行AE', '2022/1/23', '1000', '2022/1/23', '1');
INSERT INTO test.test_ks (s_id, __rid, jg, clrq, sxed, bgsj, rk) VALUES ('小明', '1111', '商业银行EC', '2022/1/24', '2000', '2022/1/23', '1');
INSERT INTO test.test_ks (s_id, __rid, jg, clrq, sxed, bgsj, rk) VALUES ('小明', '1111', '商业银行BD', '2022/1/25', '3000', '2022/1/23', '1');
INSERT INTO test.test_ks (s_id, __rid, jg, clrq, sxed, bgsj, rk) VALUES ('小明', '1111', '商业银行ED', '2022/1/26', '4000', '2022/1/23', '1');
INSERT INTO test.test_ks (s_id, __rid, jg, clrq, sxed, bgsj, rk) VALUES ('小明', '1111', '商业银行ED', '2022/1/27', '5000', '2022/1/23', '1');
INSERT INTO test.test_ks (s_id, __rid, jg, clrq, sxed, bgsj, rk) VALUES ('小明', '2222', '商业银行AE', '2022/1/23', '1000', '2022/1/1', '2');
INSERT INTO test.test_ks (s_id, __rid, jg, clrq, sxed, bgsj, rk) VALUES ('小明', '2222', '商业银行EC', '2022/1/24', '2000', '2022/1/1', '2');
INSERT INTO test.test_ks (s_id, __rid, jg, clrq, sxed, bgsj, rk) VALUES ('小明', '2222', '商业银行BD', '2022/1/25', '3000', '2022/1/1', '2');
INSERT INTO test.test_ks (s_id, __rid, jg, clrq, sxed, bgsj, rk) VALUES ('小明', '2222', '商业银行ED', '2022/1/26', '4000', '2022/1/1', '2');
INSERT INTO test.test_ks (s_id, __rid, jg, clrq, sxed, bgsj, rk) VALUES ('小白', '3333', '商业银行DE', '2022/1/28', '7000', '2022/1/23', '1');

尝试生成一些字段：

drop table if exists test.dw_report_person_others_info;
create table if not exists test.dw_report_person_others_info
    engine = MergeTree
        order by id
as
select rowNumberInAllBlocks() + 1                                              as id,
       s_id,
       maxIf(all_fkjg, t1.rk = '1')                                              as this_month_fkjg,
       maxIf(all_fkjg, t1.rk = '2')                                              as last_month_fkjg,
       maxIf(all_fkjg_name, t1.rk = '1')                                         as this_month_fkjg_name,
       maxIf(all_fkjg_name, t1.rk = '2')                                         as last_month_fkjg_name,
       arrayFilter(x->
                       not has(last_month_fkjg, x), this_month_fkjg)           as new_fkjg_tmp
from (select distinct s_id,__rid, rk from test.test_ks) t1
         left join (select __rid,
             groupUniqArray(concat(clrq, ',', sxed)) as all_fkjg,
               groupUniqArray(concat(clrq, ',', sxed,',',jg))     as all_fkjg_name
      from test.test_ks
      group by __rid) t2
on t1.__rid = t2.__rid
group by s_id;

借助上表的结果，同时用arrayJoin函数来过滤新开信用卡的银行，

select s_id, arrayStringConcat(groupUniqArray(name),',')
from (select s_id,
             arrayJoin(this_month_fkjg_name) as t1,
             arrayJoin(all_fkjg)             as t2,
             arrayJoin(new_fkjg_tmp)         as t3,
             splitByChar(',', t1)[3]         as name
      from (select s_id,
                   this_month_fkjg_name,
                   arrayMap(x ->concat(splitByChar(',', x)[1], ',', splitByChar(',', x)[2]),
                            this_month_fkjg_name) as all_fkjg,
                   new_fkjg_tmp
            from test.dw_report_person_others_info) t
      where t2 = t3
        and concat(splitByChar(',', t1)[1], ',', splitByChar(',', t1)[2]) = t2
         ) t1
group by s_id;

发现可以达到预期的效果：