学习笔记-sql 篇

group by

SELECT BIGINT(self_gds_id % 10) item_group
,CASE WHEN BIGINT(self_gds_id % 10) IN (0,3,4,5,6,9) THEN '人工定价' ELSE '算法定价' END type --error
FROM jiuwu_sc.ads_spc_service_recycle_process_ds
WHERE self_platform_on_shelf_time >= '2023-05-19 00:00:00' and dt = MAX_PT("jiuwu_sc.ads_spc_service_recycle_process_ds")
GROUP BY (self_gds_id % 10)
LIMIT 5;

不会报错

但是

SELECT BIGINT(self_gds_id % 10) item_group
,CASE WHEN BIGINT(self_gds_id) % 10 IN (0,3,4,5,6,9) THEN '人工定价' ELSE '算法定价' END type --error
FROM jiuwu_sc.ads_spc_service_recycle_process_ds
WHERE self_platform_on_shelf_time >= '2023-05-19 00:00:00' and dt = MAX_PT("jiuwu_sc.ads_spc_service_recycle_process_ds")
GROUP BY (self_gds_id % 10)
LIMIT 5;

会报错
FAILED: ODPS-0130071:[2,26] Semantic analysis exception - column reference ads_spc_service_recycle_process_ds.self_gds_id should appear in GROUP BY key

https://blog.csdn.net/tanqingfu1/article/details/124267435
1、order by后面的列必须是在select后面存在的。
2、 select、having或order by后面存在的非聚合列必须全部在group by中存在。