SQL大宝剑--已燃尽所有SQL的理解
京
京东物流 向往约 10 分钟
京东物流 向往约 10 分钟为此，基于个人经验、理解与实践，我总结了一些方法和技巧，能让SQL尽量变得优雅，即兼顾代码可读性和执行性能两方面的提升。
1.子查询与谓词下推
很多同事在写关联逻辑时，习惯于直接将原表关联，随后在最下方用一大段WHERE语句进行条件过滤，如下示例：
// -------------------- Bad Codes ------------------------SELECTf1.pin,c1.site_id,c2.site_nameFROMfdm.fdm1 AS f1LEFT JOIN cdm.cdm1 AS c1ONf1.erp = lower(c1.account_number)LEFT JOIN cdm.cdm2 AS c2ONc1.site_id = c2.site_codeWHEREf1.start_date <= '""" + start_date + """'AND f1.end_date > '""" + start_date + """'AND f1.status = 1AND c1.dt = '""" + start_date + """'AND c2.yn = 1GROUP BYf1.pin,c1.site_id,c2.site_name
这段SQL主要有两个问题：
如果使用上述示例的写法，主要关注的是LEFT OUTER JOIN时WHERE语句里的条件是否会引起谓词不下推。如果不想记这些看起来很复杂的规则怎么办？可以如下所示直接使用子查询：
// -------------------- Good Codes 👍🏻------------------------SELECTf1.pin,c1.site_id,c2.site_nameFROM(SELECT erp, pin FROM fdm.fdm1 WHERE dp = 'ACTIVE' AND status = 1)f1LEFT JOIN(SELECTsite_id,lower(account_number) AS account_numberFROMcdm.cdm1WHEREdt = '""" + start_date + """')c1ONf1.erp = c1.account_numberLEFT JOIN(SELECT site_code, site_name FROM cdm.cdm2 WHERE yn = 1)c2ONc1.site_id = c2.site_codeGROUP BYf1.pin,c1.site_id,c2.site_name
将原来WHERE语句里的各个条件下推到每个表的子查询中，可以先过滤掉不必要的行，提升关联效率。同时可读性大大提高，能清晰地看出每个来源表都取了哪些数据。还有一些其它细节，比如BDP平台的fdm拉链表，大部分业务场景下，都可以用dp='ACTIVE'代替start_date <= '""" + start_date + """' AND end_date > '""" + start_date + """'。同时注意列裁剪问题，尽量少用SELECT * FROM，只选取必要的列以减少内存开销。
2.去重难题
为了保证数据粒度的准确，几乎所有的SQL脚本编写时，都要考虑去重问题。常见的方法有：
GROUP BY
DISTINCT
ROW_NUMBER开窗
COLLECT_SET
我们经常能在各种大数据技术分享中看到去重时推荐使用GROUP BY代替DISTINCT的观点。不可否认，数据量达到一定程度，去重字段枚举值也很复杂时，GROUP BY确实在性能上更优秀，同时可以避免数据倾斜。但具体情况具体分析，比如下面两段SQL涉及的业务场景：
// --------- Good Codes 👍🏻--------selectcount(distinct ulp_base_age)fromapp.app1wheredt = sysdate(-1)// --------- Bad Codes --------selectcount(ulp_base_age)from(selectulp_base_agefromapp.app1wheredt = sysdate(-1)group byulp_base_age) t
在面对更复杂的数据集时，去重也需要更巧妙的方法。假设有一个数据量极大的页面埋点数据集，其部分数据如下所示：
| click_dt | pin |
| 2024-12-16 | a |
| 2024-12-16 | a |
| 2024-12-16 | a |
| 2024-12-16 | bb |
| 2024-12-16 | bb |
| 2024-12-16 | ccc |
| 2024-12-16 | ccc |
| 2024-12-16 | dddd |
| 2024-12-16 | eee |
| 2024-12-16 | eeee |
// -------------------- Bad Codes --------------------selectclick_dt,count(distinct pin)as uvfromlog_tablegroup byclick_dt;
可以看到所有数据都被分配到了同一个桶里，其它桶都闲置，明显造成效率低下。优化代码如下：
// -------------------- Good Codes 👍🏻 --------------------SELECTclick_dt,size(collect_set(pin)) AS uvFROM(SELECT click_dt, pin FROM log_table GROUP BY click_dt, pin)tmpGROUP BYclick_dt;
// ------------------- Even Better Codes 👍🏻👍🏻👍🏻 -------------------SELECTclick_dt,SUM(uv_tmp) AS uvFROM(SELECTlen_pin,click_dt,size(collect_set(pin)) AS uv_tmpFROM(SELECT click_dt, pin, LENGTH(pin) AS len_pin FROM log_table)log_table_tmpGROUP BYlen_pin,click_dt)tmpGROUP BYclick_dt
3.充分使用平台工具
比如在任务调度的py脚本里，可以利用sys.argv来控制时间参数。sys.argv的第一个元素是默认的，内容为脚本名称。而通过判断sys.argv的长度，可以在SQL内容之前使用如下Python代码来设置参数：
if len(sys.argv) == 1:# BDP不传参数的情况下使用，仅适用于BDP线上调度curday = ht.oneday(0)today = datetime.datetime.strptime(curday, '%Y-%m-%d')start_date = str((today + datetime.timedelta(days=-1)).strftime("%Y-%m-%d"))[0:10]end_date = str(today)[0:10]last31Day = start_dateelif len(sys.argv) == 2:# BDP线上调度使用 配合BDP参数 ${fmt(add(NTIME(),-1,'day'),'yyyy-MM-dd')}end_date = str(datetime.datetime.strptime(sys.argv[1], "%Y-%m-%d"))[0:10]start_date = str((datetime.datetime.strptime(end_date, "%Y-%m-%d")).replace(day=1))[0:10]last31Day = (datetime.datetime.strptime(end_date, "%Y-%m-%d") +datetime.timedelta(days=-30)).strftime("%Y-%m-%d")elif len(sys.argv) == 3:# 回刷使用，直接调用python脚本，并且需要传递两个日期参数，开始日期，结束日期start_date = str(datetime.datetime.strptime(sys.argv[1], "%Y-%m-%d"))[0:10]end_date = str(datetime.datetime.strptime(sys.argv[2], "%Y-%m-%d"))[0:10]else:print('parameter error')sys.exit(1)
if (len(sys.argv) == 1) | (len(sys.argv) == 2):ht.exec_sql(schema_name='app',# 补数调度# sql=showsql.format(htYDay_B=start_date, htYDay=end_date),# 批量调度sql=showsql.format(htYDay_B=start_date, htYDay=end_date),table_name='app1',exec_engine='spark',spark_resource_level='high',retry_with_hive=False,spark_args=['--conf spark.sql.hive.mergeFiles=true','--conf spark.sql.adaptive.enabled=true','--conf spark.sql.adaptive.repartition.enabled=true','--conf spark.sql.adaptive.join.enabled=true','--conf spark.sql.adaptive.skewedJoin.enabled=true','--conf spark.hadoop.hive.exec.orc.split.strategy=ETL','--conf spark.sql.shuffle.partitions=1200','--conf spark.driver.maxResultSize=8g','--conf spark.executor.memory=32g'])elif len(sys.argv) == 3:ht.exec_sql(schema_name='app',# 补数调度sql=showsql.format(htYDay_B=start_date, htYDay=end_date),# 批量调度# sql=showsql.format(htYDay_B=last31Day, htYDay=end_date),table_name='app1',exec_engine='spark',spark_resource_level='high',retry_with_hive=False,spark_args=['--conf spark.sql.hive.mergeFiles=true','--conf spark.sql.adaptive.enabled=true','--conf spark.sql.adaptive.repartition.enabled=true','--conf spark.sql.adaptive.join.enabled=true','--conf spark.sql.adaptive.skewedJoin.enabled=true','--conf spark.hadoop.hive.exec.orc.split.strategy=ETL','--conf spark.sql.shuffle.partitions=1200','--conf spark.driver.maxResultSize=8g','--conf spark.executor.memory=32g'])else:print('parameter error')sys.exit(1)
IF的第一个分支的作用是线上调度任务不配置参数时，可以将昨天的日期和今天的日期赋值给htYDay_B和htYDay；第二个分支则是线上脚本配置${fmt(add(NTIME(),-1,'day'),'yyyy-MM-dd')}等参数时，可以根据该参数计算并赋值htYDay_B和htYDay；第三个分支是任务补录时使用，通过上传时间范围的开始时间和结束时间，直接赋值htYDay_B和htYDay，来控制脚本中取数时间范围。
推荐阅读
Daniel H. Wagner Prize | 京东供应链创新与实践：应用数据驱动的库存选品和调拨算法提升履约效率