在 echart 等业务场景下, 查询N天的数据时, 希望返回横坐标一天一行的, 一共N行数据.
但是从数据库 group by 出来的数据可能因部分日期数据缺失不够7行, 此时需要将没有数据的日期填充进来.
说明
处理前的数据
select date, sum(v) as v from [{date:'2021-12-03',v:3},{date:'2021-12-06',v:6}] group by date
| date | v |
|---|---|
| 2021-12-03 | 3 |
| 2021-12-06 | 3 |
处理思路
使用 factory 函数构造一张临时表, 只有时间这一个字段. 然后以此临时表作为主表去 join 数据库查来的表.
-- 先构造 timeScope 变量, 用于控制时间范围
set st='2021-12-01', et='2021-12-07', timeScope=concat(@st,'~',@et);
-- 使用这个时间范围构造出只有时间列的临时表
select * from factory({engine:'timescope', key:'date', timeScope:${timeScope}});
-- 查询数据库
select date, sum(v) as v from [{date:'2021-12-03',v:3},{date:'2021-12-06',v:6}] group by date;
-- join 数据
left join x(*) using(date);
处理结果
| date | v |
|---|---|
| 2021-12-01 | |
| 2021-12-02 | |
| 2021-12-03 | 3 |
| 2021-12-04 | |
| 2021-12-05 | |
| 2021-12-06 | 6 |
| 2021-12-07 |
factory 函数说明
factory 函数是一个 from 语句专用函数, 用于调用不同的数据引擎, 生产各种临时数据.
实际调用的数据引擎由为参数: engine
engine=timescope 的用法
| 参数 | 值举例 | 必填 | 说明 |
|---|---|---|---|
| engine | timescope | 必填 | 数据引擎 |
| key | date | 必填 | 临时表的列名 |
| timeScope | 2021-12-16~2021-12-19 | 必填 | 时间范围 开始时间~结束时间 或者 开始时间~结束时间~时间间隔 |
| timeScopeType | day / week / month / year | 默认 day | 时间周期 |
| valueType | date / string | 默认 string | 时间列的数据类型 |
| dateFormat | yyyy-MM-dd | 默认 yyyy-MM-dd | 文本型时, 用于指定时间格式 |
- 举例
-- 按日
select * from factory({engine:'timescope', key:'date', timeScope:'2021-12-16~2021-12-19', timeScopeType:'day', valueType:'string', dateFormat:'yyyy-MM-dd'}) where date >= '2021-12-18'
-- 返回:[{"date":"2021-12-18"},{"date":"2021-12-19"}]
-- 按周, 注意: timeScope 第一天是周几, 后续返回的时间就全是周几
select * from factory({engine:'timescope', key:'date', timeScope:'2021-11-29~2021-12-31', timeScopeType:'week', valueType:'string', dateFormat:'yyyy-MM-dd'}) where date >= '2021-12-18'
-- 返回: [{"date":"2021-12-20"},{"date":"2021-12-27"}];
-- 按周, 强制返回周一
select * from factory({engine:'timescope', key:'date', timeScope:'2021-12-01~2021-12-31', timeScopeType:'week', valueType:'string', dateFormat:'yyyy-MM-dd@周初'}) where date >= '2021-12-18'
-- 返回: [{"date":"2021-12-20"},{"date":"2021-12-27"}];
-- 按月
select * from factory({engine:'timescope', key:'date', timeScope:'2021-01-03~2021-12-31', timeScopeType:'month', valueType:'string', dateFormat:'yyyy-MM-dd'}) where date >= '2021-10-18'
-- 返回: [{"date":"2021-11-01"},{"date":"2021-12-01"}];
-- 按年
select * from factory({engine:'timescope', key:'date', timeScope:'2015-01-03~2021-12-31', timeScopeType:'year', valueType:'string', dateFormat:'yyyy-MM-dd'}) where date >= '2019-10-18'
-- 返回: [{"date":"2020-01-01"},{"date":"2021-01-01"}];
