思路一:表自连接
这种方案能搜到很多类似的回答,点击这个链接。这种方案可以用户一般的业务系统,但是数据量一旦提升还是有一定的查询压力的。这里不多阐述。
思路二:采用窗口函数
oracle有lag和lead窗口函数,配合over(partition by order by)使用。示例文章点击这里。
这里大家都会碰到一个问题就是,时间节点补全的问题。由于数据原因,数据不是每天连续的数据,就会导致在行偏移进行计算的时候,会出现偏移错位的情形,这样计算出来的数据就会出现错误。比如数据库只存在2019-01-27和2019-01-29的数据,在计算偏移的时候就会出现29号和27号进行环比计算的bug,针对这个问题,我们可以采取自主补全时间节点的方案。
建立时间段临时时间表。例如从1900-01-01到2099-12-31号的数据提前录入数据库,然后通过join或者union进行关联查询。这样就会模拟做出每一天的数据,从而避免上面的问题,这里会出现的问题有两个,数据量加大会使查询变慢,第二个问题是两个月份天数不等会出现行偏移错误,所以需要把每个月全部补全,比如就会出现2019-02-31这种数据。然后通过判断最终再把数据给清除掉。这种方案的弊端就是有点凑的感觉,而且维度出现除时间之外的另一个维度的时候,会出现只有时间维度补全了,但是其他维度的内容并没有补全,所以这种方案只适用于单时间维度查询。
SELECT
*
FROM
(
SELECT
tdate,
sex,
v,
lag (v, 1, NULL) over (PARTITION by sex ORDER BY tdate) prev,
(
v - lag (v, 1, NULL) over (PARTITION by sex ORDER BY tdate)
) xx
FROM
(
SELECT
tdate,
sex,
v
FROM
(
SELECT
DATE_FORMAT(create_date, '%Y-%m-%d') AS tdate,
sex as sex,
count(customer_id) AS v
FROM
tf_f_customer t
GROUP BY
DATE_FORMAT(create_date, '%Y-%m-%d'),
sex
UNION ALL
SELECT
temp_date AS tdate,
temp_value as sex,
count(temp_value) AS v
FROM
temp_date temp_date
GROUP BY
temp_date,
temp_value
) AS temp
ORDER BY
tdate
) td
ORDER BY
td.tdate
) result
WHERE
v != 0
AND prev != 0
AND v IS NOT NULL
AND prev IS NOT NULL
思路三:窗口函数变种。
这种思路是受一些开源OLAP引擎的启发。先附上sql。
SELECT
CONCAT(
td. YEAR,
'年',
td. MONTH,
'月',
td. DAY,
'日'
) AS field02676,
IFNULL(td.field11917, 0.00) field11917,
IFNULL(td.field85970, 0.00) field85970
FROM
(
SELECT
result. YEAR,
result. MONTH,
result. DAY,
result.WEEKTH,
result. WEEK,
IF (
DATEDIFF(
CONCAT(
result. YEAR,
'-',
result. MONTH,
'-',
result. DAY
),
lag (
CONCAT(
result. YEAR,
'-',
result. MONTH,
'-',
result. DAY
),
1,
NULL
) over (
ORDER BY
result. YEAR,
result. MONTH,
result. DAY
)
) = 1,
(
field11917 - lag (result.field11917, 1, NULL) over (
ORDER BY
result. YEAR,
result. MONTH,
result. DAY
)
),
NULL
) field11917,
field85970
FROM
(
SELECT
tmp. YEAR,
tmp. MONTH,
tmp. DAY,
tmp.WEEKTH,
tmp. WEEK,
SUM(`skjq4td7dv`) AS field11917,
SUM(`skjq4td7dv`) AS field85970
FROM
(
SELECT
DATE_FORMAT(skhl8envgz, '%Y') AS YEAR,
DATE_FORMAT(skhl8envgz, '%m') AS MONTH,
DATE_FORMAT(skhl8envgz, '%d') AS DAY,
DATE_FORMAT(skhl8envgz, '%u') AS WEEKTH,
DATE_FORMAT(skhl8envgz, '%w') AS WEEK,
t.*
FROM
skuyjsemhf t
) tmp
GROUP BY
tmp. YEAR,
tmp. MONTH,
tmp. DAY,
tmp.WEEKTH,
tmp. WEEK
ORDER BY
tmp. YEAR,
tmp. MONTH,
tmp. DAY,
tmp.WEEKTH,
tmp. WEEK
) result
GROUP BY
result. YEAR,
result. MONTH,
result. DAY,
result.WEEKTH,
result. WEEK
) td
WHERE
(
field11917 IS NOT NULL
OR field85970 IS NOT NULL
)
讲解一下sql组成。
第一层select:是将需要的时间字段分别拆解成年、月、日、季度、周等,这里需要根据不同的时间格式来进行拆分。
第二层select:就是讲自己的业务需求加入,主要是分组信息group by和where筛选条件。并且要根据上一步的时间拆分进行字段排序。
第三层select:进行行偏移计算。
第四层select:进行数据还原,只获取自己需要的信息。
这里面有几个关键点:
1.第二层里面的排序,需要根据所需要的时间展示格式进行排序
2.第二层里面的偏移计算,partition by后面增加除时间字段外的其他维度,order by后需要按照拆解的时间字段进行排序,将可以进行偏移计算的行放在一起。
3.第二层里面进行偏移计算的时候,需要判断一下上下两行是不是可以进行比较的,如果是环比则需要看看是不是临近的两天或者两周、两月、两年等等。必要的时候需要自己开发自定义函数进行判断。
思路四:使用MDX语句
比如使用使用mondrian,这种适用于大数据分析,笔者没有进行深入研究,有需求的大家可以去学习下。