MySQL中按月统计并逐月累加统计值的几种写法

这篇具有很好参考价值的文章主要介绍了MySQL中按月统计并逐月累加统计值的几种写法。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

有时候,我们可能有这样的场景,需要将销量按月统计,并且按月逐月累加。写惯了GROUP BY,按月统计倒是小case,但是逐月累加实现起来,要稍微麻烦一点。下面就整理几种写法,以备不时之需。

本月第一天

-- 本月第一天
SELECT DATE_ADD(CURDATE(), INTERVAL -DAY(CURDATE()) + 1 DAY);
 
-- 本月第一天
SELECT CONCAT(DATE_FORMAT(CURDATE(), '%Y-%m'), '-01');

建表及模拟数据

-- 创建表
CREATE TABLE `sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sales_date` date NOT NULL,
  `sales_amount` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入模拟数据
INSERT INTO `sales` (`sales_date`, `sales_amount`) VALUES
('2023-01-01', 1500.00),
('2023-01-02', 1800.00),
('2023-01-05', 2200.00),
('2023-02-01', 1200.00),
('2023-02-03', 1800.00),
('2023-03-01', 2500.00),
('2023-03-05', 2800.00),
('2023-03-08', 3200.00),
('2023-04-01', 2100.00),
('2023-04-03', 1900.00),
('2023-04-05', 2600.00),
('2023-05-01', 3100.00),
('2023-05-02', 3400.00),
('2023-06-01', 3800.00),
('2023-06-06', 4200.00);

该表包含三个字段:id、sales_date、sales_amount。id为自增长主键,sales_date为销售日期,sales_amount为销售额。插入了15条模拟数据,涵盖了2023年1月至6月的销售数据

一、自连接和子查询

首先在内部查询中计算出每个月份的销售总额和月份;接着在外部查询中使用自连接和子查询计算每个月份的累计销售额

SELECT t1.month, t1.monthly_sales,
       SUM(t2.monthly_sales) AS cumulative_sales
FROM (
  SELECT DATE_FORMAT(sales_date, '%Y-%m') AS month,
         SUM(sales_amount) AS monthly_sales
  FROM sales
  GROUP BY month
) t1
JOIN (
  SELECT DATE_FORMAT(sales_date, '%Y-%m') AS month,
         SUM(sales_amount) AS monthly_sales
  FROM sales
  GROUP BY month
) t2 ON t1.month >= t2.month
GROUP BY t1.month;

mysql 按月统计,mysql,android,数据库

二、子查询

SELECT month, monthly_sales,
       (SELECT SUM(monthly_sales) 
        FROM (
          SELECT DATE_FORMAT(sales_date, '%Y-%m') AS month, SUM(sales_amount) AS monthly_sales
          FROM sales
          GROUP BY month
        ) t2
        WHERE t2.month <= t1.month) AS cumulative_sales
FROM (
  SELECT DATE_FORMAT(sales_date, '%Y-%m') AS month, SUM(sales_amount) AS monthly_sales
  FROM sales
  GROUP BY month
) t1;

mysql 按月统计,mysql,android,数据库

此处使用了两个子查询,第一个子查询用于获取每个月份的总销售额和月份,第二个子查询用于计算累加值。在内部子查询中,通过<=操作符将当前月份以及之前所有月份的销售额相加,从而得到累加值

三、子查询+变量

SELECT month, monthly_sales, @cumulative := @cumulative + monthly_sales AS cumulative
FROM (
  SELECT DATE_FORMAT(sales_date, '%Y-%m') AS month, SUM(sales_amount) AS monthly_sales
  FROM sales
  GROUP BY month
) t1
CROSS JOIN (SELECT @cumulative := 0) t2;

mysql 按月统计,mysql,android,数据库

此处使用了两个子查询,第一个子查询用于获取每个月份的总销售额和月份,第二个子查询用于初始化变量@cumulative。在外部查询中,通过CROSS JOIN将两个子查询连接起来,并且使用变量@cumulative来计算累加值。

四、用户变量和子查询

在内部查询中先对销售日期进行排序,然后使用用户变量@cumulative来记录每个月份的累加值。在最终的查询结果中,输出月份、当月销售额以及累加值

SELECT month, monthly_sales,
       (@cumulative := @cumulative + monthly_sales) AS cumulative_sales
FROM (
  SELECT DATE_FORMAT(sales_date, '%Y-%m') AS month, 
         SUM(sales_amount) AS monthly_sales
  FROM sales
  GROUP BY month
  ORDER BY sales_date ASC
) t1, (SELECT @cumulative := 0) t2;

mysql 按月统计,mysql,android,数据库

此处使用了两个子查询,第一个子查询用于获取每个月份的总销售额和月份,并按销售日期升序排序;第二个子查询用于初始化用户变量@cumulative。在外部查询中,通过,连接两个子查询,并使用用户变量@cumulative来计算每个月份的累加值。

五、表达式(CTE)和窗口函数

使用MySQL 8.0引入的通用表表达式(CTE)和窗口函数,可以将累加值计算放在CTE中完成

   
WITH monthly_sales AS (
  SELECT DATE_FORMAT(sales_date, '%Y-%m') AS month, 
         SUM(sales_amount) AS monthly_sales
  FROM sales
  GROUP BY month
)
SELECT month, monthly_sales,
       SUM(monthly_sales) OVER (ORDER BY month) AS cumulative_sales
FROM monthly_sales;

mysql 按月统计,mysql,android,数据库

此处使用了CTE来计算每个月份的总销售额和月份,并在外部查询中使用窗口函数SUM() OVER()对月份进行累加。

SUM() OVER()

使用MySQL 8.0引入的LATERAL关键字,以及OVER ORDER BY子句,按月份求和,再用SUM() OVER()进行累加,并分别输出月份、当月销售金额和累计销售金额

 
##月统计
SELECT  month, monthly_sales,
       SUM(monthly_sales) OVER (ORDER BY month) AS cumulative_sales
FROM (
  SELECT DATE_FORMAT(sales_date, '%Y-%m') AS month,
         SUM(sales_amount) AS monthly_sales
  FROM sales
  GROUP BY month
) t1;

##日统计
SELECT  day, monthly_sales,
       SUM(monthly_sales) OVER (ORDER BY day) AS cumulative_sales
FROM (
  SELECT DATE_FORMAT(sales_date, '%Y-%m-%d') AS day,
         SUM(sales_amount) AS monthly_sales
  FROM sales
  GROUP BY day
) t1;

mysql 按月统计,mysql,android,数据库文章来源地址https://www.toymoban.com/news/detail-736595.html

到了这里,关于MySQL中按月统计并逐月累加统计值的几种写法的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 统计文本词频的几种方法(Python)

    目录 1. 单句的词频统计 2. 文章的词频统计 方法一:运用集合去重方法 方法二:运用字典统计 方法三:使用计数器 词频统计是自然语言处理的基本任务,针对一段句子、一篇文章或一组文章,统计文章中每个单词出现的次数,在此基础上发现文章的主题词、热词。 思路:首

    2024年02月04日
    浏览(46)
  • Mysql 优化的几种方法

    MySQL是当前最流行的一个关系型数据库管理系统,广泛应用于各种互联网应用场景中。然而在实际的开发和运维过程中,我们经常会遇到一些性能问题,如查询慢、请求堆积等,需要通过MySQL优化来解决。本文将详细讲解MySQL优化的原理、方法和工具,帮助读者全面了解和掌握

    2024年02月05日
    浏览(33)
  • 【Spark手机流量日志处理】使用SparkSQL按月统计流量使用量最多的用户

    🚀 作者 :“大数据小禅” 🚀 文章简介 :本篇文章属于Spark系列文章,专栏将会记录从spark基础到进阶的内容 🚀 内容涉及到Spark的入门集群搭建,核心组件,RDD,算子的使用,底层原理,SparkCore,SparkSQL,SparkStreaming等,Spark专栏地址.欢迎小伙伴们订阅💪 SparkSQL简介 Spark

    2023年04月15日
    浏览(47)
  • MySQL加密的几种常见方式

    MySQL提供了多种加密方式来保护数据的安全性。下面是几种常见的MySQL加密方式: 密码加密: MySQL5.7及以上版本使用SHA-256算法对密码进行加密。这种加密方式更安全,可以防止密码泄露。 之前的MySQL版本使用SHA-1算法进行密码加密。这种加密方式相对较弱,不建议使用。 数据

    2024年02月09日
    浏览(52)
  • MySQL同步ES的几种方案

    MySQL数据同步ES的几种方案 1. 同步双写         与业务耦合深,且业务响应时间长 2. 异步双写         这时可以使用类似MQ这样的中间件,业务主写时向MQ发送一条信息,再由一个聚合服务区消费,最终同步到ES 3. 定时任务         不好配置时间,频率高会出现明显的

    2024年01月20日
    浏览(39)
  • 【Mysql系列】mysql中删除数据的几种方法

    在MySQL数据库中,删除数据是一个常见的操作,它允许从表中移除不再需要的数据。在执行删除操作时,需要谨慎,以免误删重要数据。 以下是MySQL中删除数据的几种方法: DELETE语句 DROP TABLE语句 TRUNCATE TABLE 使用外键约束 DELETE语句是最常用的删除数据方法之一。它允许您根据

    2024年02月05日
    浏览(41)
  • 详解MySQL索引失效的几种情况

    MySQL索引是提高查询效率的重要手段。索引失效会导致查询效率下降,甚至全表扫描,影响数据库性能。以下是可能导致MySQL索引失效的情况: 当where语句中使用 or 操作符并且 or 两边的条件涉及到至少两个字段时,MySQL无法使用索引,会转向全表扫描。因此,应尽量避免使用

    2024年02月01日
    浏览(47)
  • mysql复制表的几种常用方法

    遇到需要拷贝一个表及其数据的情况,总结了一下几种方法 1.使用 show create table 旧表 将结果拷贝出来,将旧表名换成新表名即可. 注意:该方法仅适用于拷贝表结构,不需要同步数据的情况 2.create table 新表 like 旧表 该语句将完全拷贝旧表结构, 如果需要同步数据,只需执行 insert…

    2024年01月16日
    浏览(53)
  • 35.MySQL导出数据的几种方式

    1.导出全表数据。 select * from test into outfile \\\'/tmp/a.sql\\\'; 2.导出某个数据库下的表。 --secure-file-priv=\\\'\\\'  mysqldump -T /data/backup -u root -prootroot --set-gtid-purged=OFF  test 将test数据库导出到:backup目录下。 3.导出自定义格式的文件。 mysql -uroot -prootroot -e \\\"select * from t2;\\\" test t2.sql  id    nam

    2024年02月04日
    浏览(48)
  • Mysql关联查询的几种方式(详解)

    在关系型数据库中,要进行多表查询,一般都会使用join连接。join连接的本质其实类似与集合的交并操作,Mysql实现关联查询常见的几种方式如下; 目录 1、左连接 2、右连接 3、内连接 4、全连接 5、左外连接 6、右外连接 7、全外连接 本次测试表结构和数据: A表 B表   概念说

    2024年02月08日
    浏览(46)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包