运用场景
需要查询表的某个字段的具体位数的值是多少时
用法:
substr(example,pos,length);
example:需要查询的字段名
pos:查询该字段具体的第XX位(注:这里的pos是从1开始,不是0)
length:查询从第XX位开始,长度为length的字符
示例
时间字段数据为:create_time=‘2021-02-05 10:30:00’
SELECT
'4.新增数数量_区间最大秒' AS 指标,
'20210101-202101231' AS 时间段,
times,
max(num) AS num
FROM
(
SELECT
SUBSTR(create_time, 1, 19) times,
count(DISTINCT id) AS num
FROM
db.t_user
WHERE
substr(create_time, 1, 10) BETWEEN '2021-01-01'
AND '2021-12-31'
AND sex= 'male'
AND is_flag = '1'
GROUP BY
SUBSTR(create_time, 1, 19)
) t