如何编写查询来提取ksql中所有字段值以及满足条件的值

7fhtutme 于 2021-06-07 发布在 Kafka

关注(0)|答案(1)|浏览(383)

使用以下字段创建流

CREATE STREAM pageviews_original_string(view_time string, user_id varchar, pageid varchar) WITH (kafka_topic='pageviews',value_format='DELIMITED',KEY='pageid');

将pageid与以下值一起更改为大写。

create stream up_case AS SELECT UCASE(pageid), user_id FROM PAGEVIEWS_ORIGINAL_STRING where user_id = 'User_9';

结果

PAGE_26 | User_9
PAGE_67 | User_9
PAGE_39 | User_9
PAGE_80 | User_9
PAGE_40 | User_9
PAGE_92 | User_9

现在我想要的是满足条件的数据必须被修改和提取，以及像这样的剩余字段值


****PAGE_26 | User_9

PAGE_67 | User_9
PAGE_39 | User_9
PAGE_80 | User_9
PAGE_40 | User_9
PAGE_92 | User_9****
Page_66 | User_7
Page_25 | User_2
Page_41 | User_3
Page_34 | User_1
Page_28 | User_2
Page_55 | User_5
Page_77 | User_5
Page_32 | User_8
Page_60 | User_4

你能帮我解决这个用例吗

apache-kafka ksqldb confluent-platform streaming-analytics

来源：https://stackoverflow.com/questions/53663725/how-to-write-the-query-to-extract-all-the-field-value-along-with-condition-satis

1条答案

按热度按时间

n53p2ov01#

步骤1：创建流，条件是更改用户9的pageid的大写

create stream step1 AS SELECT UCASE(pageid) AS upcase, user_id FROM PAGEVIEWS_ORIGINAL_STRING where user_id = 'User_9';

步骤2：然后创建另一个有条件的流，不改变所有用户的anycase，排除用户9

create stream step2 AS select pageid AS upcase, user_id from PAGEVIEWS_ORIGINAL_STRING where user_id != 'User_9';

然后创建另一个流来合并这两个流

ksql> create stream merge_two AS select step1;
insert into merge_two select * from step2;

结果

1544165217141 | 50671 | Page_35 | User_2
1544165217103 | 50661 | Page_10 | User_6
1544165217580 | 50681 | Page_54 | User_2
1544165217699 | 50691 | Page_45 | User_6
1544165218104 | 50701 | Page_20 | User_8
1544165218511 | 50711 | Page_13 | User_2
1544165218931 | 50721 | Page_45 | User_4
1544165219146 | 50731 | Page_68 | User_8
1544165219505 | 50741 | Page_65 | User_4
1544165219713 | 50751 | Page_74 | User_8
1544165219787 | 50761 | Page_30 | User_5
1544165220104 | 50771 | Page_72 | User_2
1544165220421 | 50781 | Page_39 | User_2
1544165220523 | 50791 | Page_55 | User_8
1544165220645 | 50801 | Page_76 | User_6
1544165220818 | 50821 | Page_46 | User_5
1544165220781 | 50811 | Page_60 | User_4
1544165220911 | 50831 | Page_24 | User_4
1544165221102 | 50841 | PAGE_11 | User_9
1544165221377 | 50851 | PAGE_95 | User_9
1544165221685 | 50861 | PAGE_56 | User_9
1544165222164 | 50871 | Page_50 | User_3
1544165222233 | 50881 | Page_12 | User_8
1544165222500 | 50891 | Page_18 | User_3
1544165222913 | 50901 | Page_67 | User_5
1544165223237 | 50911 | Page_30 | User_3
1544165223665 | 50921 | Page_62 | User_8

赞(0）回复(0）举报 2021-06-07

我来回答

如何编写查询来提取ksql中所有字段值以及满足条件的值

1条答案

相关问题

热门标签

最新问答