草庐IT

mysql - mysql中一组行的累计和

coder 2023-10-04 原文

我有一个复杂的查询(包含多个连接、联合)，它返回一组包含 id、day、hr、amount 的行。查询的输出如下所示:

id day    hr  amount 
1   1      1   10       
1   1      2   25       
1   1      3   30        
1   2      1   10       
1   2      2   40       
1   2      2   30        
2   1      1   10       
2   1      2   15        
2   1      3   30       
2   2      1   10       
2   2      2   20      
2   2      2   30

我需要为一天中的每个小时找到每个 ID 的累计总数。输出应该是这样的:

id day    hr  amount cumulative total
1   1      1   10       10
1   1      2   25       35
1   1      3   30       65 
1   2      1   10       10
1   2      2   40       50
1   2      2   30       80 
2   1      1   10       10
2   1      2   15       25 
2   1      3   30       55
2   2      1   10       10
2   2      2   20       30
2   2      2   30       60

我生成第一个输出的初始查询如下所示:

select id, day, hr, amount from
( //multiple joins on multiple tables)a
left join
(//unions on multiple tables)b
on a.id=b.id;

获取第二个输出中描述的累计和的 sql 查询是什么？解决方案中不应使用 SET。

谢谢。

最佳答案

更新

MySQL 8.0 引入了“窗口函数”，功能等同于 SQL Server“窗口函数”(使用 Transact-SQL OVER 语法提供分区和排序)，以及 Oracle“分析函数”。

MySQL 引用手册 12.21 窗口函数 https://dev.mysql.com/doc/refman/8.0/en/window-functions.html

此处提供的答案是针对 8.0 之前的 MySQL 版本的方法。

原始答案

MySQL 不提供可用于获取运行“累积和”的类型分析函数，就像其他 DBMS(如 Oracle 或 SQL Server)中可用的分析函数一样。

但是，可以使用 MySQL 模拟一些分析功能。

(至少)有两种可行的方法:

一种是使用相关子查询来获取小计。如果外部查询的谓词很复杂，这种方法在大型集合上可能会很昂贵，而且会很复杂。这实际上取决于“多个表上的多个连接”的复杂程度。 (不幸的是，MySQL 也不支持 CTE。)

另一种方法是利用MySQL用户变量，做一些控制中断处理。这里的“技巧”是对查询的结果进行排序(使用 ORDER BY)，然后将查询包装在另一个查询中。

我将给出后一种方法的示例。

由于 MySQL 执行操作的顺序，cumulative_total 列需要在 id 和 day 来自当前的值之前计算行被保存到用户变量中。将此列放在最前面是最简单的。

别名为 i 的内联 View (在下面的查询中)只是用来初始化用户变量，以防这些已经在 session 中设置。如果那些已经赋值，我们想忽略它们的当前值，最简单的方法是初始化它们。

您的原始查询被括在括号中，并在下面的示例中被赋予一个别名 c。对原始查询的唯一更改是添加了 ORDER BY 子句，因此我们可以确保按顺序处理查询中的行。

外部选择检查当前行的 id 和 day 值是否“匹配”前一行。如果是，我们将当前行的 amount 添加到累计小计中。如果它们不匹配，那么我们将累计小计重置为零，并添加当前行中的金额(或者，更简单地说，只需分配当前行中的金额)。

在完成累计总数的计算后，我们将当前行的id 和day 值保存到用户变量中，以便在我们处理时使用它们下一行。

例如:

SELECT IF(@prev_id = c.id AND @prev_day = c.day
         ,@cumtotal := @cumtotal + c.amount
         ,@cumtotal := c.amount) AS cumulative_total
     , @prev_id  := c.id  AS `id`
     , @prev_day := c.day AS `day`
     , c.hr
     , c.amount AS `amount'
  FROM ( SELECT @prev_id  := NULL
              , @prev_day := NULL
              , @subtotal := 0
       ) i
  JOIN (

         select id, day, hr, amount from
         ( //multiple joins on multiple tables)a
         left join
         (//unions on multiple tables)b
         on a.id=b.id

         ORDER BY 1,2,3
       ) c

如果需要以不同的顺序返回列，将累计总数作为最后一列，那么一个选择是将整个语句包装在一组括号中，并将该查询用作内联 View :

SELECT d.id
     , d.day
     , d.hr
     , d.amount
     , d.cumulative_total
FROM (
       // query from above
     ) d

关于mysql - mysql中一组行的累计和，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/17664436/

mysql中codeamountdaysqljoincumulative-sum

有关mysql - mysql中一组行的累计和的更多相关文章

  1. 使用canal同步MySQL数据到ES - 2

    文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co

  2. python ffmpeg 使用 pyav 转换 一组图像 到 视频 - 2

    2022/8/4更新支持加入水印水印必须包含透明图像，并且水印图像大小要等于原图像的大小pythonconvert_image_to_video.py-f30-mwatermark.pngim_dirout.mkv2022/6/21更新让命令行参数更加易用新的命令行使用方法pythonconvert_image_to_video.py-f30im_dirout.mkvFFMPEG命令行转换一组JPG图像到视频时，是将这组图像视为MJPG流。我需要转换一组PNG图像到视频，FFMPEG就不认了。pyav内置了ffmpeg库，不需要系统带有ffmpeg工具因此我使用ffmpeg的python包装p

  3. ruby-on-rails - 无法安装 mysql2 0.3.14 gem - 2

    我看到其他人也遇到过类似的问题，但没有一个解决方案对我有用。0.3.14gem与其他gem文件一起存在。我已经完全按照此处指示完成了所有操作:https://github.com/brianmario/mysql2.我仍然得到以下信息。我不知道为什么安装程序指示它找不到include目录，因为我已经检查过它存在。thread.h文件存在，但不在ruby​​目录中。相反，它在这里:C:\RailsInstaller\DevKit\lib\perl5\5.8\msys\CORE\我正在运行Windows7并尝试在Aptana3中构建我的Rails项目。我的Ruby是1.9.3。$gemin

  4. ruby - 如何使用 ruby​​ mysql2 执行事务 - 2

    我已经开始使用mysql2gem。我试图弄清楚一些基本的事情——其中之一是如何明确地执行事务(对于批处理操作，比如多个INSERT/UPDATE查询)。在旧的ruby-mysql中，这是我的方法:client=Mysql.real_connect(...)inserts=["INSERTINTO...","UPDATE..WHEREid=..",#etc]client.autocommit(false)inserts.eachdo|ins|beginclient.query(ins)rescue#handleerrorsorabortentirelyendendclient.commi

  5. ruby-on-rails - 当我通过 rvm 使用 rails3 时，如何在 ubuntu 上安装 mysql2 gem？ - 2

    我正在尝试绕过rails配置这个极其复杂的迷宫。到目前为止，我设法在ubuntu上设置了rvm(出于某种原因，ruby在ubuntu存储库中已经过时了)。我设法建立了一个Rails项目。我希望我的测试项目使用mysql而不是mysqlite。当我尝试“rakedb:migrate”时，出现错误:“!!!缺少mysql2gem。将其添加到您的Gemfile:gem'mysql2'”当我尝试“geminstallmysql”时，出现错误，告诉我需要为安装命令提供参数。但是，参数列表很大，我不知道该选择哪些。如何通过在ubuntu上运行的rvm和mysql获取rails3？谢谢。

  6. ruby - 用一个 ＃!在 Ubuntu 中运行的 ruby​​ 中评论 - 2

    我是编程新手，正在尝试遵循使用#!用ruby评论。我一直收到消息:bash:matz.rb:找不到命令我正在使用这个评论:#!/usr/bin/envruby我试过有和没有后面的空格!以及有和没有环境。当我使用$哪个rubyruby在:/usr/bin/ruby我还进入了操作系统，将所有用户对文件matz.rb的权限更改为rwx，但没有任何效果。是我做错了什么还是我的系统设置不正确？ 最佳答案 /usr/bin/env部分没问题。运行时需要为bash提供matz.rb的路径。如果您在matz.rb所在的目录中，请键入“./matz.

  7. Ruby 相当于 "grep -C 5"以获取匹配项周围的行的上下文？ - 2

    我搜索了一下，但我一定是使用了错误的术语-ruby​​是否有办法grep查找字符串/正则表达式并返回周围的5行(上方和下方)？我知道我可以调用"grep-C5..."或什至编写我自己的方法，但这似乎是ruby​​应该有的东西，我只是没有使用正确的搜索词。 最佳答案 您可以使用正则表达式来完成。这是我们要搜索的字符串:s=%{ThefirstlineThesecondlineThethirdlineThefourthlineThefifthlineThesixthlineTheseventhlineTheeightlineThenin

  8. Centos7-yum安装mysql-修改密码-无密码登录-安全配置 - 2

    目录1、yum安装mysql修改密码(1)在mysql里面修改(2)第二种方式，利用mysqladmin修改密码2、没有密码，登录mysql修改密码3、mysql的安全设置1、yum安装mysql在CentOS中默认安装有MariaDB（MySQL的一个分支），安装完成之后可以直接覆盖MariaDB。rpm-qa|grepmariadb查询是否安装了mariadbrpm-e--nodepsmariadb-libs-5.5.60-1.el7_5.x86_64卸载mariadwgethttp://dev.mysql.com/get/mysql57-community-release-el7-11.

  9. Ruby:是否可以在不对类名进行硬编码的情况下确定我的 Ruby 方法在其中执行的类？ - 2

    我是Ruby的Nuby。我正在寻找一种方法来获取当前执行行的方法的包含类对象。如果不对类名进行硬编码，这可能吗？#hardcodedexampleclassAdefto_s"Iama"+A.to_s#Class"A"ishardcodedhere.IsthereanotherwaytoreferencetheclassA?endend我想也许self.class会起作用，但是当类被子类化时，它并没有给我我想要的东西。#FollowingOutputs=>IamaCamelIamaCamelIamaCamel#butIwant=>IamaCamelIamaMammalIamaAnimal

  10. ruby - 安装 dm-mysql-adapter 时出错 - 2

    我是Ruby的新手。我安装了DataMapper并且正在尝试安装dm-mysql-adapter-1.0.2gem。但是当我尝试安装时，出现以下错误。我正在使用ubuntu操作系统。vinoth@vinoth-laptop:~/Downloads$geminstalldm-mysql-adapter-1.0.2----with-mysql-lib=/usr/lib/mysql----with-mysql-conf=/usr/bin/mysqlWARNING:Installingto~/.gemsince/home/vinoth/gemsand/home/vinoth/gems/bina

随机推荐