使用 SQL 进行同比分析
最编程
2024-05-02 09:44:06
...
场景描述
在做数据分析时,经常会需要展示同比和环比的数据,可以通过 SQL 来完成同比和环比的计算,再配合数据可视化工具进行展示,每个月都会自动展示当月数据与上月的对比(环比),以及当月数据与去年同期数据的对比(同比);
效果展示
我先看一下将同比、环比的数据放到数据看板上的效果图:
上图是将某公司的销售数据做了同比和环比分析后,通过报表展示出来的效果,包含:线上线下销量的同比环比、各大区的销量月环比。
实现过程
测试数据预览
以下是我们分析的原始数据,数据来源于真实业务记录,所以部分数据进行了遮挡处理;
我们重点关注的数据:
- 每条数据代表一单销售业绩;
- “类型” 包含线上、线下;
- “成交日期” 是分析依据;
- “大区” 也是要分析的内容。
SQL实现过程
我们以 “线上销量的月环比” 做SQL实现步骤讲解
先统计每个月的线上销量
select DATE_FORMAT(日期,'%Y-%m') as date, count(*) as 累计销量 from renwu where 类型 = '线上' GROUP BY date
我们需要在每个月累计销量 后面 显示一列上月的销量:
select t.date tdate ,t.累计销量 本月销量 ,y.累计销量 上月销量
from
(select DATE_FORMAT(日期,'%Y-%m') as date, count(*) as 累计销量 from renwu where 类型 = '线上' GROUP BY date) t
left JOIN
(select DATE_FORMAT(DATE_ADD(日期,INTERVAL 1 MONTH),'%Y-%m') as date, count(*) as 累计销量 from renwu where 类型 = '线上' GROUP BY date) y
on t.date = y.date
最后就可以通过 “本月销量” 和 “上月销量” 计算环比了;
计算公式为:(本月销量 - 上月销量) / 上月销量 *100%
select t.date tdate ,t.累计销量 本月销量 ,y.累计销量 上月销量,
case
when y.累计销量 is null or y.累计销量 = 0 then 0.00
else round((t.累计销量 - y.累计销量) / y.累计销量, 4)
end ratio
from
(select DATE_FORMAT(日期,'%Y-%m') as date, count(*) as 累计销量 from renwu where 类型 = '线上' GROUP BY date) t
left JOIN
(select DATE_FORMAT(DATE_ADD(日期,INTERVAL 1 MONTH),'%Y-%m') as date, count(*) as 累计销量 from renwu where 类型 = '线上' GROUP BY date) y
on t.date = y.date
计算结果中的 “ratio” 就是计算的环比,这里保留了4位小数,是为了在报表中转化为百分比再保留两位小数。
我们只需要展示最新月份的环比,所以不需要把所有数据都列出来,只需要展示最新月份的一条数据即可,做最后的优化:
以上是环比计算的过程,同比计算只需要将增加的 “上月数据” 改为 “去年数据“ 即可
”DATE_FORMAT(DATE_ADD(日期,INTERVAL 1 MONTH),'%Y-%m')”
改为
”DATE_FORMAT(DATE_ADD(日期,INTERVAL 1 YEAR),'%Y-%m')”
展示同比环比
在 DataEase 的仪表板编辑界面,将SQL 的计算数据放到一个指标卡中显示即可,显示类型选择为 百分比显示,效果如下:
最终数据展示看板
以上看板展示工具为:DataEase
上一篇: 字节缓冲流
推荐阅读
-
35 岁实现财务*,腾讯程序员手握2300万提前退休?-1000万房产、1000万腾讯股票、加上300万的现金,一共2300万的财产。有网友算了一笔账,假设1000万的房产用于自住,剩下1300万资产按照平均税后20-50万不等进行计算,大约花上26-60年左右的时间才能赚到这笔钱。也就是说,普通人可能奋斗一辈子,才能赚到这笔钱。在很多人还在为中年危机而惶惶不可终日的时候,有的人的35岁,就已经安全着陆,试问哪个打工人不羡慕?但问题是有这样财富积累必然有像样的实力做靠山。没有人可以不劳而获。 看到这里,肯定有人说,那么对于普通人来说,卷可能真就成了唯一的出路。但是卷也有轻松的卷,“偷懒”的卷法,对于程序员而言,刨除掉一时无法改掉的开会传统占用的大部分时间,如何把有限的时间和精力放在真正重要的架构设计、需求设计上,而不是重复的造*,编码、改bug、手动测试。因此在科技改变生活的今天,学会使用AI工具成为程序员们的必备技能。 以全栈式全自动的软件开发工具飞算SoFlu软件机器人为例,作为全球首款面向微服务架构设计和最佳实践的软件机器人,SoFlu软件机器人改变了原来手工编码的作业模式,通过可视化拖拽方式以及参数配置就能实现等同于编写复杂代码的业务逻辑,在设计业务逻辑时就完成了微服务应用开发,做到“业务即图,图即代码”,实现“软件开发,十倍提效”。 而作为飞算SoFlu软件机器人的一个重要组成部分,FuncGPT(慧函数)支持所有类型函数创建。通过自然语言描述Java函数需求,实时生成高质量、高可读性的Java函数代码。生成代码可直接复制到IDEA,或一键导入Java全自动开发工具函数库。 FuncGPT(慧函数)具有五大核心能力: ● 自然语言:通过自然语言即可生成函数,降低软件开发门槛。 ● 秒级函数开发:全自动秒级开发函数,秒级完成,效率千倍提升。 ● 一站式函数开发应用:函数生成即可直接应用,效率、安全有保障。 ● 质量可靠:生成的函数代码符合业界规范、具备良好的可读性,符合最优解。 ● 灵活生成:可以根据开发人员的具体需求生成并支持调整和修改。 以“将文件流转换为File对象”这个需求为例,以下是FuncGPT(慧函数)秒级生成的代码: / 类名:FileConverter // 函数名:convertStreamToFile // 函数功能:将文件流转换为File对象 // POM依赖包:无 import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; public class FileConverter { /** * 将文件流转换为File对象 * @param inputStream 文件流 * @param filePath 文件路径 * @param fileName 文件名 * @return 转换后的File对象 * @throws IOException 如果发生I/O异常 */ public static File convertStreamToFile(InputStream inputStream, String filePath, String fileName) throws IOException { File file = new File(filePath + File.separator + fileName); // 创建File对象 try (FileOutputStream outputStream = new FileOutputStream(file)) { // 创建文件输出流 byte buffer = new byte[1024]; int bytesRead; while ((bytesRead = inputStream.read(buffer)) != -1) { // 从文件流读取数据并写入文件 outputStream.write(buffer, 0, bytesRead); } } return file; // 返回转换后的File对象 } } // 函数示例 // 将文件流转换为File对象示例 // 入参:inputStream,文件流 // 入参:filePath,文件路径 // 入参:fileName,文件名 // 出参:file,转换后的File对象 // 调用示例: // InputStream inputStream = new FileInputStream("example.txt"); // String filePath = "C:\\Users\\User\\Documents"; // String fileName = "example.txt"; // File file = FileConverter.convertStreamToFile(inputStream, filePath, fileName); // System.out.println(file.getAbsolutePath); // 输出结果:例如,将文件流转换为File对象后,文件的绝对路径为:C:\Users\User\Documents\example.txt // 则输出结果为:C:\Users\User\Documents\example.txt 通过分析,不难发现以上代码:
-
使用 ClickHouse("窗口功能")快速实施同比和环比分析
-
使用 SQL 进行同比分析
-
对 GreenDao3 的使用进行全面分析
-
使用 R 语言进行简单的主成分分析 (PCA)
-
文本分析--使用 jieba 库进行中文分词和去活字(附案例研究)
-
如何使用 SPSS 进行相关分析
-
正负偏差变量 即 d2+、d2- 分别表示决策值中超出和未达到目标值的部分。而 di+、di- 均大于 0 刚性约束和目标约束(柔性目标约束有偏差) 在多目标规划中,>=/<= 在刚性约束中保持不变。当需要将约束条件转换为柔性约束条件时,需要将 >=/<= 更改为 =(因为已经有 d2+、d2- 用来表示正负偏差),并附加上 (+dii-di+) 注意这里是 +di、-di+!之所以是 +di,-di+,是因为需要将目标还原为最接近的原始刚性约束条件 优先级因素和权重因素 对多个目标进行优先排序和优先排序 目标规划的目标函数 是所有偏差变量的加权和。值得注意的是,这个加权和都取最小值。而 di+ 和 dii- 并不一定要出现在每个不同的需求层次中。具体分析需要具体问题具体分析 下面是一个例子: 题目中说设备 B 既要求充分利用,又要求尽可能不加班,那么列出的时间计量表达式即为:min z = P3 (d3- + d3 +) 使用 + 而不是 -d3 + 的原因是:正负偏差不可能同时存在,必须有 di+di=0 (因为判定值不可能同时大于目标值和小于目标值),而前面是 min,所以只要取 + 并让 di+ 和 dii- 都为正值即可。因此,得出以下规则: 最后,给出示例和相应的解法: 问题:某企业生产 A 和 B 两种产品,需要使用 A、B、C 三种设备。下表显示了与工时和设备使用限制有关的产品利润率。问该企业应如何组织生产以实现下列目标? (1) 力争利润目标不低于 1 500 美元; (2) 考虑到市场需求,A、B 两种产品的生产比例应尽量保持在 1:2; (3)设备 A 是贵重设备,严禁超时使用; (4)设备 C 可以适当加班,但要控制;设备 B 要求充分利用,但尽量不加班。 从重要性来看,设备 B 的重要性是设备 C 的三倍。 建立相应的目标规划模型并求解。 解:设企业生产 A、B 两种产品的件数分别为 x1、x2,并建立相应的目标计划模型: 以下为顺序求解法,利用 LINGO 求解: 1 级目标: 模型。 设置。 variable/1..2/:x;! s_con_num/1...4/:g,dplus,dminus;!所需软约束数量(g=dplus=dminus 数量)及相关参数; s_con(s_con_num);! s_con(s_con_num,variable):c;!软约束系数; 结束集 数据。 g=1500 0 16 15. c=200 300 2 -1 4 0 0 5; 结束数据 min=dminus(1);!第一个目标函数;!对应于 min=z 的第一小部分;! 2*x(1)+2*x(2)<12;!硬约束 @for(s_con_num(i):@sum(variable(j):c(i,j)*x(j))+dminus(i)-dplus(i)=g(i)); !使用设置完成的数据构建软约束表达式; ! !软约束表达式 @for(variable:@gin(x)); !将变量约束为整数; ! 结束 此时,第一级目标的最优值为 0,第一级偏差为 0: 第二级目标: !求 dminus(1)=0,然后求解第二级目标。 模型。 设置。 变量/1..2/:x;!设置:变量/1..2/:x; ! s_con_num/1...4/:g,dplus,dminus;!软约束数量及相关参数; s_con(s_con_num(s_con_num));! s_con(s_con_num,variable):c;! 软约束系数; s_con(s_con_num,variable):c;! 结束集 数据。 g=1500 0 16 15; c=200 300 2 -1 4 0 0 5; 结束数据 min=dminus(2)+dplus(2);!第二个目标函数 2*x(1)+2*x(2)<12;!硬约束 @for(s_con_num(i):@sum(variable(j):c(i,j)*x(j))+dminus(i)-dplus(i)=g(i)); ! 软约束表达式;! dminus(1)=0; !第一个目标结果 @for(variable:@gin(x)); ! 结束 此时,第二个目标的最优值为 0,偏差为 0: 第三目标 !求 dminus(2)=0,然后求解第三个目标。 模型。 设置。 变量/1..2/:x;!设置:变量/1..2/:x; ! s_con_num/1...4/:g,dplus,dminus;!软约束数量及相关参数; s_con(s_con_num(s_con_num));! s_con(s_con_num,variable):c;! 软约束系数; s_con(s_con_num,variable):c;! 结束集 数据。 g=1500 0 16 15; c=200 300 2 -1 4 0 0 5; 结束数据 min=3*dminus(3)+3*dplus(3)+dminus(4);!第三个目标函数。 2*x(1)+2*x(2)<12;!硬约束 @for(s_con_num(i):@sum(variable(j):c(i,j)*x(j))+dminus(i)-dplus(i)=g(i)); ! 软约束表达式;! dminus(1)=0; !第一个目标约束条件; ! dminus(2)+dplus(2)=0; !第二个目标约束条件 @for(variable:@gin(x));! 结束 最终结果为 x1=2,x2=4,dplus(1)=100,最优利润为
-
使用 Python 11.6 进行数据分析的重采样和频率转换 (I)
-
5.1.3 边界值法--二元基本边界值分析 边界值测试的另一个关键假设是,故障很少是由两个(或两个以上)缺陷同时出现造成的,这在可靠性理论中称为 "单一缺陷 "假设。基于 "单一缺陷 "假设的边界值测试称为基本边界值分析。 在边界值测试中,我们通常使用两值边界,然后辅助以正常值来设计输入变量的值。 对于只有 x 和 y 两个输入变量的软件,输入域在二维坐标系中呈阴影状。使用基本边界值分析法得到的测试用例就是黑点所在的位置,总共有九个测试用例。 如果有一个 n 变量的软件输入域,则选择其中一个变量略小于最小值、最小值、正常值、最大值和略大于最大值这五个值,其余全部取正常值。对这个 n 个变量的软件输入域进行边界值分析,可产生 4n+1 个测试用例。 三值基本边界值分析