需求：
统计商品浏览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进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
目录
