8.1.6. LSQL 与传统的SQL写法对比
| 对比点 | 传统SQL | LSQL |
|---|---|---|
| 统计计数 | SELECT COUNT(*) FROM EXAMPLE_SHU WHERE partition= '2015' ; |
① LSQL 层实现
SELECT COUNT(*) FROM EXAMPLE_SHU WHERE partition = '2015'; ② SPRAK 层实现 SELECT COUNT(*) FROM (SELECT * FROM EXAMPLE_SHU WHERE partition = '2015')TMP ; |
| 单列GROUP BY | SELECT SEX,COUNT(*),COUNT(AMTDOUBLE),S UM(AMTDOUBLE) FROM EXAMPLE_SHU WHERE partition = '3000W' GROUP BY SEX LIMIT 10 ; |
① LSQL 层实现SELECT
SEX,COUNT(*),COUNT(AMTDOUBLE),SUM(AMTDOUBLE)
FROM EXAMPLE_SHU WHERE partition = '3000W' GROUP BY SEX LIMIT 10 ② SPRAK 层实现 SELECT SEX,COUNT(*),COUNT(AMTDOUBLE),SUM(AMTDOUBLE) FROM(SELECT SEX, AMTDOUBLE FROM EXAMPLE_SHU WHERE partition = '3000W' ) TMP GROUP BY SEX LIMIT 10 |
| 多列GROUP BY | SELECT SEX,PROVINCE,COUNT(*) AS CNT,COUNT(AMTDOUBLE),SUM(AMTDOUBLE) FROM EXAMPLE_SHU WHERE partition = '3000W' GROUP BY SEX,PROVINCE ORDER BY CNT DESC LIMIT 10 ; |
① LSQL 层实现
SELECT SEX,PROVINCE,COUNT(*) AS CNT,COUNT(AMTDOUBLE),SUM(AMTDOUBLE) FROM
EXAMPLE_SHU WHERE partition = '3000W' GROUP BY SEX,PROVINCE ORDER BY CNT DESC LIMIT 10 ② SPRAK 层实现 SELECT SEX,PROVINCE,COUNT(*) AS CNT,COUNT(AMTDOUBLE),SUM(AMTDOUBLE) FROM (SELECT SEX,PROVINCE,AMTDOUBLE FROM EXAMPLE_SHU WHERE partition = '3000W' ) TMP GROUP BY SEX,PROVINCE ORDER BY CNT DESC LIMIT 10 |
| LEFT JOIN | SELECT K21.VEHICLEPLATE FROM K21 LEFT JOIN K22 ON K21.VEHICLEPLATE=K22.VEHICLEPLATE ORDER BY K21.VEHICLEPLATE ; | WITH K23 AS (SELECT VEHICLEPLATE,TOLLCODE FROM K21 WHERE partition = '3000W'), K24 AS ( SELECT VEHICLEPLATE,TOLLCODE FROM K22 WHERE partition = '3000W') SELECT K23.VEHICLEPLATE FROM K23 LEFT JOIN K24 ON K23.VEHICLEPLATE=K24.VEHICLEPLATE ORDER BY K23.VEHICLEPLATE; |
| UNION ALL | SELECT COUNT(*) AS CNT FROM EXAMPLE_SHU WHERE partition = '3000W' UNION ALL SELECT COUNT(*) AS CNT FROM EXAMPLE_SHU WHERE partition = '300WINSERT' ; | WITH K23 AS (SELECT COUNT(*) AS CNT FROM K21 WHERE partition = '3000W' ), K24 AS (SELECT COUNT(*) AS CNT FROM K22 WHERE partition = '3000W' ) SELECT * FROM K23 UNION ALL SELECT * FROM K24 ; |
| DISTINCT | SELECT SIZE(COLLECT_SET(SEX)) AS DIST_SEX, SIZE(COLLECT_SET(PROVINCE)) AS DIST_PROVINCE, COUNT(*) AS CNT, COUNT(AMTLONG) AS CNT_LONG, COUNT(DISTINCT AMTLONG) AS DIST_LONG FROM EXAMPLE_SHU WHERE partition = '3000W' AND CONTENT='王老吉' ; | SELECT SIZE(COLLECT_SET(TMP.SEX)) AS DIST_SEX, SIZE(COLLECT_SET(TMP.PROVINCE)) AS DIST_PROVINCE, COUNT(*) AS CNT, COUNT(TMP.AMTLONG) AS CNT_LONG, COUNT(DISTINCT TMP.AMTLONG) AS DIST_LONG FROM ( SELECT SEX,PROVINCE,AMTLONG FROM EXAMPLE_SHU WHERE partition = '3000W' AND CONTENT='王老吉' ) TMP LIMIT 10; |
| GROUP BY +DISTINCT | SELECT SEX AS SEX, SIZE(COLLECT_SET(PROVINCE)) AS DIST_PROVINCE, COUNT(*) AS CNT, COUNT(AMTLONG) AS CNT_LONG, COUNT(DISTINCT AMTLONG) AS DIST_LONG FROM EXAMPLE_SHU WHERE partition = '3000W' AND CONTENT=' 王老吉' GROUP BY SEX LIMIT 10; | SELECT TMP.SEX AS SEX, SIZE(COLLECT_SET(TMP.PROVINCE)) AS DIST_PROVINCE, COUNT(*) AS CNT, COUNT(TMP.AMTLONG) AS CNT_LONG, COUNT(DISTINCT TMP.AMTLONG) AS DIST_LONG FROM ( SELECT SEX,PROVINCE,AMTLONG FROM EXAMPLE_SHU WHERE partition = '3000W' AND CONTENT='王老吉' ) TMP GROUP BY TMP.SEX LIMIT 10; |
| 结果返回 | 1. 统计返回为 0 时，显示为 0 2. 查询返回为空时，显示为空 | 1. 统计返回为 0 时，显示为空 2. 查询返回为空时，显示为空 |