需求：
统计商品浏览top20、加入购物车top20、购买商品top20
在GuassDB T 1.0.2分布式集群环境下，分组降序排序取top20，通过union all 后结果集不正确，结果显示的是升序排列的前top20
单机下SQL结果集没问题
问题已反馈华为。 华为建议在 1.0.2 B319 版本下测试没问题，因此建议使用1.0.2 B319以上版本。
单机下：
--建表SQL
create table user_behavior_log(
event_time VARCHAR2(30) not null,
event_type VARCHAR2(30) not null,
product_id VARCHAR2(50),
category_id VARCHAR2(50),
category_code VARCHAR2(50),
brand VARCHAR2(30),
price VARCHAR2(20),
user_id VARCHAR2(20),
user_session VARCHAR2(50)
);
--单机版本
SQL> select * from v$version;
VERSION
----------------------------------------------------------------
GaussDB_T_1.0.2.B307 Release d4484ac
ZENGINE
2 rows fetched.
Elapsed: 0.118 sec
SQL>
--查询SQL语句结果集正确
SQL> select * from (select product_id,'view' as event_type,count(1) event_count from user_behavior_log where event_type='view' group by product_id order by event_count desc limit 20) a
union all
select * from (select product_id,'cart' as event_type,count(1) event_count from user_behavior_log where event_type='cart' group by product_id order by event_count desc limit 20) b
union all
select * from (select product_id,'purchase' as event_type,count(1) event_count from user_behavior_log where event_type='purchase' group by product_id order by event_count desc limit 20) c
;
PRODUCT_ID EVENT_TYPE EVENT_COUNT
-------------------------------------------------- ---------- --------------------
1004856 view 86928
1005115 view 78540
1004767 view 77314
4804056 view 46256
1004870 view 40883
1004833 view 40749
1004249 view 40084
1005105 view 39611
1005160 view 36268
1002544 view 35880
5100816 view 29526
1004873 view 29117
1005100 view 29088
1004836 view 29023
1004741 view 24413
1004785 view 22990
1002524 view 22236
1005239 view 21379
1004838 view 21252
1004750 view 20848
1004856 cart 25321
1005115 cart 22592
1004767 cart 22490
4804056 cart 13472
1004833 cart 11889
1004870 cart 11775
1004249 cart 11695
1005105 cart 11475
1005160 cart 10574
1002544 cart 10389
1004873 cart 8479
1004836 cart 8408
5100816 cart 8403
1005100 cart 8388
1004741 cart 7059
1004785 cart 6774
1002524 cart 6390
1005239 cart 6162
1004838 cart 6132
1004750 cart 5961
1004856 purchase 27449
1005115 purchase 24629
1004767 purchase 24301
4804056 purchase 14722
1004870 purchase 12971
1004833 purchase 12863
1004249 purchase 12609
1005105 purchase 12457
1005160 purchase 11380
1002544 purchase 11216
5100816 purchase 9251
1004873 purchase 9222
1004836 purchase 9164
1005100 purchase 9122
1004741 purchase 7635
1004785 purchase 7223
1002524 purchase 6994
1005239 purchase 6830
1004838 purchase 6660
1004750 purchase 6558
60 rows fetched.
Elapsed: 57.558 sec
SQL>
分布式集群下：
--建表SQL
create table user_behavior_log(
event_time VARCHAR2(30) not null,
event_type VARCHAR2(30) not null,
product_id VARCHAR2(50),
category_id VARCHAR2(50),
category_code VARCHAR2(50),
brand VARCHAR2(30),
price VARCHAR2(20),
user_id VARCHAR2(20),
user_session VARCHAR2(50)
)
distribute by list(event_type)
(
groupid 1 values ('view'),
groupid 2 values ('cart'),
groupid 3 values ('purchase'),
groupid 4 values ('remove_from_cart')
);
--版本
SQL> select * from v$version;
VERSION
----------------------------------------------------------------
GaussDB_T_1.0.2.B307 Release d4484ac
ZENGINE
2 rows fetched.
Elapsed: 0.137 sec
SQL>
--SQL查询语句结果集错误
SQL> select * from (select product_id,'view' as event_type,count(1) event_count from user_behavior_log where event_type='view' group by product_id order by event_count desc limit 20) a
union all
select * from (select product_id,'cart' as event_type,count(1) event_count from user_behavior_log where event_type='cart' group by product_id order by event_count desc limit 20) b
union all
select * from (select product_id,'purchase' as event_type,count(1) event_count from user_behavior_log where event_type='purchase' group by product_id order by event_count desc limit 20) c
;
PRODUCT_ID EVENT_TYPE EVENT_COUNT
-------------------------------------------------- ---------- --------------------
100000024 view 25
100000043 view 22
100000030 view 20
100000027 view 13
100000023 view 12
100000045 view 12
100000046 view 11
100000049 view 11
100000036 view 9
100000044 view 5
100000000 view 4
100000009 view 4
100000032 view 3
100000042 view 3
100000010 view 2
100000047 view 2
100000041 view 2
100000011 view 2
100000014 view 1
100000025 view 1
100000043 cart 5
100000024 cart 4
100000030 cart 4
100000023 cart 3
100000045 cart 3
100000036 cart 3
100000009 cart 2
100000032 cart 2
100000046 cart 2
100000051 cart 2
100000042 cart 2
100000011 cart 2
100000000 cart 1
100000010 cart 1
100000027 cart 1
100000041 cart 1
100000044 cart 1
100000049 cart 1
100000052 cart 1
100000057 cart 1
100000024 purchase 10
100000043 purchase 8
100000030 purchase 4
100000066 purchase 4
100000045 purchase 4
100000044 purchase 3
100000046 purchase 3
100000053 purchase 3
100000049 purchase 3
100000009 purchase 2
100000057 purchase 2
100000027 purchase 2
100000036 purchase 2
100000010 purchase 1
100000023 purchase 1
100000032 purchase 1
100000041 purchase 1
100000052 purchase 1
100000054 purchase 1
100000067 purchase 1
60 rows fetched.
Elapsed: 43.601 sec
SQL> select * from (select product_id,'view' as event_type,count(1) event_count from user_behavior_log where event_type='view' group by product_id order by event_count desc limit 20) a;
PRODUCT_ID EVENT_TYPE EVENT_COUNT
-------------------------------------------------- ---------- --------------------
1004856 view 86928
1005115 view 78540
1004767 view 77314
4804056 view 46256
1004870 view 40883
1004833 view 40749
1004249 view 40084
1005105 view 39611
1005160 view 36268
1002544 view 35880
5100816 view 29526
1004873 view 29117
1005100 view 29088
1004836 view 29023
1004741 view 24413
1004785 view 22990
1002524 view 22236
1005239 view 21379
1004838 view 21252
1004750 view 20848
20 rows fetched.
Elapsed: 10.635 sec
SQL> select * from (select product_id,'cart' as event_type,count(1) event_count from user_behavior_log where event_type='cart' group by product_id order by event_count desc limit 20) b;
PRODUCT_ID EVENT_TYPE EVENT_COUNT
-------------------------------------------------- ---------- --------------------
1004856 cart 25321
1005115 cart 22592
1004767 cart 22490
4804056 cart 13472
1004833 cart 11889
1004870 cart 11775
1004249 cart 11695
1005105 cart 11475
1005160 cart 10574
1002544 cart 10389
1004873 cart 8479
1004836 cart 8408
5100816 cart 8403
1005100 cart 8388
1004741 cart 7059
1004785 cart 6774
1002524 cart 6390
1005239 cart 6162
1004838 cart 6132
1004750 cart 5961
20 rows fetched.
Elapsed: 3.307 sec
SQL> select * from (select product_id,'purchase' as event_type,count(1) event_count from user_behavior_log where event_type='purchase' group by product_id order by event_count desc limit 20) c;
PRODUCT_ID EVENT_TYPE EVENT_COUNT
-------------------------------------------------- ---------- --------------------
1004856 purchase 27449
1005115 purchase 24629
1004767 purchase 24301
4804056 purchase 14722
1004870 purchase 12971
1004833 purchase 12863
1004249 purchase 12609
1005105 purchase 12457
1005160 purchase 11380
1002544 purchase 11216
5100816 purchase 9251
1004873 purchase 9222
1004836 purchase 9164
1005100 purchase 9122
1004741 purchase 7635
1004785 purchase 7223
1002524 purchase 6994
1005239 purchase 6830
1004838 purchase 6660
1004750 purchase 6558
20 rows fetched.
Elapsed: 3.520 sec
SQL>
最后修改时间：2020-04-03 20:22:17
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
