7
拉埋点数据SQL三分钟速成
source link: http://openfibers.github.io/blog/2020/09/08/mai-dian-sqlsan-fen-zhong-su-cheng/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
1. 基本选取
我们现在有一个表 user_action，有如下数据：
1 2 3 4
SELECT
*
FROM user_action
;
app_id app_ver 表示 app 和其版本号
device_id 为设备id
sys_ver 为系统版本号
event_name 为具体的事件名
2. 选取特定列 (SELECT 子句)
1 2 3 4 5 6 7
SELECT
app_version,
device_id,
sys_ver,
event_name
FROM user_action
;
3. 过滤条件 (WHERE 子句)
1 2 3 4 5 6 7 8 9
SELECT
app_version,
device_id,
sys_ver,
event_name
FROM user_action
WHERE
sys_ver = '13.3'
;
WHERE 子句全部运算符：
1 2 3 4 5 6 7 8 9 10
= 等于
!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
BETWEEN 在两者之间
IS NULL 为空
IS NOT NULL 非空
LIKE 字符串包含
有些 DB 还支持：
1 2 3
<> 不等于
!< 不小于
!> 不大于
4. 组合条件 (WHERE … AND/OR/IN/NOT IN …)：
- 结合顺序：先算 AND 后算 OR
- AND 和 OR 运算符在左值满足的情况下不计算右值的行为随 DB 不同而不同，但是不关心中间过程的情况下，这个区别一般不会影响计算结果
4.1 AND
1 2 3 4 5 6 7 8 9 10 11
SELECT
app_version,
device_id,
sys_ver,
event_name
FROM user_action
WHERE
sys_ver = '14.0'
AND
event_name = 'btn2_touched'
;
4.2 IN
1 2 3 4 5 6 7 8 9 10 11
SELECT
app_version,
device_id,
sys_ver,
event_name
FROM user_action
WHERE
sys_ver = '14.0'
AND
event_name IN ['btn1_touched', 'btn2_touched']
;
4.3 NOT IN
1 2 3 4 5 6 7 8 9 10 11
SELECT
app_version,
device_id,
sys_ver,
event_name
FROM user_action
WHERE
sys_ver = '14.0'
AND
event_name NOT IN ['btn2_touched']
;
4.4 NOT
1 2 3 4 5 6 7 8 9
SELECT
app_version,
device_id,
sys_ver,
event_name
FROM user_action
WHERE
NOT sys_ver = '14.0'
;
5. 聚集函数 (COUNT,AVG,MAX,MIN,SUM) 与分组 (GROUP BY)
- SELECT 中未使用聚集函数的所有列都要写在 GROUP BY 子句里
- 如果 SELECT 的全部列都使用了聚集函数，可以不写 GROUP BY，但是输出报表时这种情况并不常见
- 表内有的两行数据行A和行B，若行A和行B在 GROUP BY 子句指定的任意一列中不一致，则行A和行B会被归到结果集不同的两行中，不被聚集
- COUNT() 接受列名或者 *。传入列名时，返回列内不为 NULL 的总数量，传入 * 时，返回列内含 NULL 的总数量
- 比如性能耗时均值，可以用 AVG 比较方便的计算出来
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
SELECT
app_version,
device_id,
sys_ver,
event_name,
COUNT(*) as 样本量
FROM user_action
WHERE
sys_ver = '13.3'
GROUP BY
app_version,
device_id,
sys_ver,
event_name
;
例子2：表内有的两行数据行A和行B，若行A和行B在 GROUP BY 子句指定的任意一列中不一致，则行A和行B会被归到结果集不同的两行中，不被聚集：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
SELECT
app_version,
device_id,
sys_ver,
event_name,
COUNT(*) as 样本量
FROM user_action
WHERE
sys_ver = '14.0'
GROUP BY
app_version,
device_id,
sys_ver,
event_name
;
6. 排序(ORDER BY)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
SELECT
app_version,
device_id,
sys_ver,
event_name,
COUNT(*) as 样本量
FROM user_action
WHERE
sys_ver = '14.0'
GROUP BY
app_version,
device_id,
sys_ver,
event_name
ORDER BY
event_name
;
7. 去除列内相同值的数据(DISTINCT)
7.1 在 COUNT/AVG/SUM 内部使用
最常见的是配合 COUNT 使用。COUNT(DISTINCT user_id) 常用于计算 UV、活跃设备数:
1 2 3 4 5 6 7 8 9 10
SELECT
app_version,
COUNT(DISTINCT device_id) as 激活设备数,
COUNT(*) as 点击数
FROM user_action
GROUP BY
app_version
;
- 在 COUNT 内部使用，仅可以匹配 COUNT(列), 不能匹配 COUNT(*)。匹配结果会包含 NULL。
- DISTINCT 除了可以作用于 COUNT/AVG/SUM，也可以作用于 MIN/MAX，但是 MIN/MAX 加 DISTINCT 没啥区别。
- 对列A 使用了 COUNT/SUM/AVG，再去 DISTINCT 列A 以外的其他列是没有意义的，因为当 COUNT/SUM/AVG 生效时，其他没写 COUNT/SUM/AVG 的列都成为了这些数字结果的属性，在结果集中已经是唯一的了。
7.2 作用于 SELECT 子句
作用于 SELECT 子句时，紧跟 SELECT 写，对所有列生效。不能对部分列生效：
例子1 单列：
1 2 3 4
SELECT DISTINCT
app_version,
FROM user_action
;
例子2 多列：
1 2 3 4 5
SELECT DISTINCT
app_version,
device_id,
FROM user_action
;
8. 限制结果集数量(LIMIT)
使用 LIMIT 一般有两种情况： * 提升执行速度 * 有数据库读取条数权限控制，不允许读出更多条
LIMIT 仅对结果集行数限制，和中间计算过程无关：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
SELECT
app_version,
device_id,
sys_ver,
event_name,
COUNT(*) as 样本量
FROM user_action
WHERE
sys_ver = '13.3'
GROUP BY
app_version,
device_id,
sys_ver,
event_name
LIMIT 1
;
索引建在经常需要 WHERE 的列上。
WHERE 中以下表达式会导致单次运算无法使用索引：
- WHERE 子句的查询条件里有 !=
- WHERE 子句使用了 Mysql 函数的时候: WHERE left(name, 4) = ‘xxx’
使用 LIKE，匹配关键字带有前置通配符时，无法使用索引:
- WHERE name LIKE ‘%Alisa%’ 无法使用
- WHERE name LIKE ‘Alisa%’ 可以使用
- WHERE name LIKE ‘%Alisa’ 无法使用
- WHERE 子句使用了 IS NULL 或者 IS NOT NULL
- WHERE 子句条件中有 OR，即使其中有条件带索引也不会使用
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK