SQLite-帮助优化具有多个条件的前几行的总计
尉迟德惠
问题内容：
问题答案：
我试图获取表中每个记录的“值”列的条件总和，其中所有“先前”记录均按相同的“类别”字段值和相同的“批准”字段值分组，然后分为负数和负数正数。
在我的程序中，用户可以按任何顺序创建文档记录，因此“上一个”定义为：
如果 Approved = TRUE，则“上一个”记录的 ApprovedDate 字段值比当前记录的字段早。如果 ApprovedDate
字段值相同，则“先前”记录的 DocumentNumber 字段值较低。
如果 已批准 = FALSE，则“先前”记录的“ IssuredDate” 字段值比当前记录的字段早。如果 IssuedDate
字段值相同，则“先前”记录的 DocumentNumber 字段值较低。
例如，在下表中：
CREATE TABLE Changes (GUID TEXT, Value REAL, DocumentNumber TEXT, Approved INTEGER, ApprovedDate TEXT, IssuedDate TEXT, Category TEXT);
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('4F7253A4E1B3D841B84D4A82B4F0E7A2', '11', 0, 18526.7, '', '2009-03-31T05:00:00Z', 'UNKNOWN');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('D97537852E927B499C21C14F3D13CF06', '1', 0, 0, '', '2008-11-10T05:00:00Z', 'UNKNOWN');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('857DADB463807345918729B33399B36F', '2', 0, 0, '', '2008-11-10T05:00:00Z', 'UNKNOWN');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('7989D242E05AFF4FB5EE99114822BF80', '21', 0, 50112, '', '2009-07-22T05:00:00Z', 'UNKNOWN');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('16A0AB27FD3A784D9E0A14406C7683E0', '3', 0, 0, '', '2009-01-15T05:00:00Z', 'UNKNOWN');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('D3D7B1C306D38C438FC3DEDFCB57D411', '131', 0, 17204, '', '2010-12-14T05:00:00Z', 'UNKNOWN');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('2C89D974DDF86743A0D7D62B385FBDEF', '147', 0, 0, '', '2010-12-01T05:00:00Z', 'UNKNOWN');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('F371D4237C837D448824697EB0162905', '198', 0, 0, '', '2011-01-10T05:00:00Z', 'UNKNOWN');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('433D64C871AE4E46A0E1BFCE2BB69BA7', '364', 0, 0, '', '2011-11-14T05:00:00Z', 'UNKNOWN');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('808496DBDE76CB4F911396BB817724F3', '352', 0, 0, '', '2011-10-17T05:00:00Z', 'UNKNOWN');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('9545DEF1666B5F4D8626F19F8E9E9333', '418', 0, 10948, '', '2012-03-07T22:19:18Z', 'UNKNOWN');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('244D7D89B79E0F4E91100E4ADB300656', '439', 0, 50945, '', '2012-04-27T20:33:26Z', 'UNKNOWN');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('115A427BBB1D2C43BA11D9E5875FAA2C', '465', 0, 480049, '', '2012-07-20T16:17:54Z', 'UNKNOWN');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('3A2271EFCC767E4CA40017E68802F10C', '478', 0, 54298, '', '2012-08-01T17:26:38Z', 'UNKNOWN');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('99D0EFC5A9F1AA498DB1A4CDF294129B', '490', 0, 11500, '', '2012-09-18T14:23:13Z', 'ALTER');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('38B2E3A379C5084998E6A84D496AC555', '491', 0, 26088, '', '2012-09-25T06:00:00Z', 'ALTER');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('8902831C8FAD4941841EE2847656BDAF', '494', 0, -825, '', '2012-10-16T14:20:06Z', 'ALTER');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('7AFDB08A002AE54A8DE7699855AEBE30', '495', 0, 221, '', '2012-10-16T14:21:27Z', 'ALTER');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('38A2CCEF5F0B294AA8B8752F461D121D', '496', 0, 0, '', '2012-12-24T01:11:15Z', 'ALTER');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('24CCD5CE409E674593108CBD816DBCCE', '486', 1, -825, '2012-10-01T21:42:52Z', '2012-09-17T20:42:12Z', 'ALTER');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('C7458704E36C8F448C1F3A485EB08304', '485', 1, 10000, '2012-10-01T21:25:56Z', '2012-09-11T21:29:44Z', 'ALTER');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('B511953AE6FB6446A63AA83C159057BE', '487', 1, 82170, '2012-10-01T21:42:51Z', '2012-09-17T20:46:41Z', 'ALTER');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('EC977BC304A971439D04BB9DF4D8188A', '488', 1, 15500, '2012-10-01T20:58:15Z', '2012-09-18T06:00:00Z', 'ALTER');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('D9B1F0C0A8E490448697B783639E09E0', '489', 1, 11503, '2012-10-01T21:42:50Z', '2012-09-18T13:56:18Z', 'ALTER');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('698BB6D65832D146A49727C717A591A1', '492', 1, 2787, '2012-10-01T21:10:06Z', '2012-09-25T15:55:02Z', 'ALTER');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('155D4F2B1854B34FABCDE8CF20F1E44C', '493', 1, 12162, '2012-10-01T21:10:06Z', '2012-09-25T16:04:40Z', 'ALTER');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('137C9BF2B1EFD34B8831ADA70C5F9431', '1', 1, 369543, '2011-12-08T13:41:04Z', '1899-12-30T05:00:00Z', 'DRAW');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('7F29FC7114BD10468AE92A047345B5DB', '2', 1, 7258, '2011-12-08T13:41:04Z', '2011-10-20T05:00:00Z', 'DRAW');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('6B66D8EAD88E6E4FA29401CD524B978A', '3', 1, 979321, '2011-12-08T13:41:04Z', '2011-11-08T05:00:00Z', 'DRAW');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('7F393B712B213041A6DD211E04F6DCA6', '4', 1, 14998, '2012-04-20T15:16:21Z', '2012-04-18T21:07:07Z', 'DRAW');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('2255F84E7C7DA04389765724872D6413', '5', 1, 58926, '2012-04-20T15:16:23Z', '2012-04-18T21:13:15Z', 'DRAW');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('DB4A5588DEB9F34C868F7AD1CB13ACC3', '6', 1, 13232, '2012-04-20T15:16:05Z', '2012-04-18T21:17:00Z', 'DRAW');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('B5231AE40F8E7D41BA0A4D09614CBDF9', '7', 1, 10176, '2012-04-20T15:16:25Z', '2012-04-18T21:19:41Z', 'DRAW');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('2362D54FCC53E447AC7D8289EA89FD05', '8', 1, 17556, '2012-04-20T15:16:04Z', '2012-04-18T21:21:20Z', 'DRAW');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('6ED4565CA041704B8D006EDA4A1E4CF9', '9', 1, 399639, '2012-05-30T16:32:43Z', '2012-05-17T06:00:00Z', 'DRAW');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('B21BE07E3E42C2418C70AD17862D3AE1', '10', 1, 6231, '2012-08-16T16:55:00Z', '2012-08-02T16:02:03Z', 'DRAW');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('8FD252A50137754A98698F93AC9B01A7', '11', 1, 629, '2012-08-16T16:54:58Z', '2012-08-02T16:07:57Z', 'DRAW');
INSERT INTO Changes (GUID, DocumentNumber, Approved, Value, ApprovedDate, IssuedDate, Category) VALUES ('1B9AFD2C20362F48A486E8A535B29AF5', '20', 1, -113810, '2011-12-13T17:15:53Z', '2010-02-10T05:00:00Z', 'UNKNOWN');
结果应为：
[结果] [2] ：
| GUID | SORTID | VALUE | POSITIVE_PREVIOUS_TOTAL | NEGATIVE_PREVIOUS_TOTAL |
----------------------------------------------------------------------------------------------------------------------------------------------
| 99D0EFC5A9F1AA498DB1A4CDF294129B | ALTER_0_2012-09-18T14:23:13Z_490 | 11500 | 0 | 0 |
| 38B2E3A379C5084998E6A84D496AC555 | ALTER_0_2012-09-25T06:00:00Z_491 | 26088 | 11500 | 0 |
| 8902831C8FAD4941841EE2847656BDAF | ALTER_0_2012-10-16T14:20:06Z_494 | -825 | 37588 | 0 |
| 7AFDB08A002AE54A8DE7699855AEBE30 | ALTER_0_2012-10-16T14:21:27Z_495 | 221 | 37588 | -825 |
| 38A2CCEF5F0B294AA8B8752F461D121D | ALTER_0_2012-12-24T01:11:15Z_496 | 0 | 37809 | -825 |
| EC977BC304A971439D04BB9DF4D8188A | ALTER_1_2012-10-01T20:58:15Z_488 | 15500 | 92170 | -825 |
| 698BB6D65832D146A49727C717A591A1 | ALTER_1_2012-10-01T21:10:06Z_492 | 2787 | 119173 | -825 |
| 155D4F2B1854B34FABCDE8CF20F1E44C | ALTER_1_2012-10-01T21:10:06Z_493 | 12162 | 121960 | -825 |
| C7458704E36C8F448C1F3A485EB08304 | ALTER_1_2012-10-01T21:25:56Z_485 | 10000 | 0 | 0 |
| D9B1F0C0A8E490448697B783639E09E0 | ALTER_1_2012-10-01T21:42:50Z_489 | 11503 | 107670 | -825 |
| B511953AE6FB6446A63AA83C159057BE | ALTER_1_2012-10-01T21:42:51Z_487 | 82170 | 10000 | -825 |
| 24CCD5CE409E674593108CBD816DBCCE | ALTER_1_2012-10-01T21:42:52Z_486 | -825 | 10000 | 0 |
| 137C9BF2B1EFD34B8831ADA70C5F9431 | DRAW_1_2011-12-08T13:41:04Z_1 | 369543 | 0 | 0 |
| 7F29FC7114BD10468AE92A047345B5DB | DRAW_1_2011-12-08T13:41:04Z_2 | 7258 | 369543 | 0 |
| 6B66D8EAD88E6E4FA29401CD524B978A | DRAW_1_2011-12-08T13:41:04Z_3 | 979321 | 376801 | 0 |
| 2362D54FCC53E447AC7D8289EA89FD05 | DRAW_1_2012-04-20T15:16:04Z_8 | 17556 | 1453454 | 0 |
| DB4A5588DEB9F34C868F7AD1CB13ACC3 | DRAW_1_2012-04-20T15:16:05Z_6 | 13232 | 1430046 | 0 |
| 7F393B712B213041A6DD211E04F6DCA6 | DRAW_1_2012-04-20T15:16:21Z_4 | 14998 | 1356122 | 0 |
| 2255F84E7C7DA04389765724872D6413 | DRAW_1_2012-04-20T15:16:23Z_5 | 58926 | 1371120 | 0 |
| B5231AE40F8E7D41BA0A4D09614CBDF9 | DRAW_1_2012-04-20T15:16:25Z_7 | 10176 | 1443278 | 0 |
| 6ED4565CA041704B8D006EDA4A1E4CF9 | DRAW_1_2012-05-30T16:32:43Z_9 | 399639 | 1471010 | 0 |
| 8FD252A50137754A98698F93AC9B01A7 | DRAW_1_2012-08-16T16:54:58Z_11 | 629 | 1876880 | 0 |
| B21BE07E3E42C2418C70AD17862D3AE1 | DRAW_1_2012-08-16T16:55:00Z_10 | 6231 | 1870649 | 0 |
| D97537852E927B499C21C14F3D13CF06 | UNKNOWN_0_2008-11-10T05:00:00Z_1 | 0 | 0 | 0 |
| 857DADB463807345918729B33399B36F | UNKNOWN_0_2008-11-10T05:00:00Z_2 | 0 | 0 | 0 |
| 16A0AB27FD3A784D9E0A14406C7683E0 | UNKNOWN_0_2009-01-15T05:00:00Z_3 | 0 | 0 | 0 |
| 4F7253A4E1B3D841B84D4A82B4F0E7A2 | UNKNOWN_0_2009-03-31T05:00:00Z_11 | 18526.69921875 | 0 | 0 |
| 7989D242E05AFF4FB5EE99114822BF80 | UNKNOWN_0_2009-07-22T05:00:00Z_21 | 50112 | 18526.69921875 | 0 |
| 2C89D974DDF86743A0D7D62B385FBDEF | UNKNOWN_0_2010-12-01T05:00:00Z_147 | 0 | 68638.69921875 | 0 |
| D3D7B1C306D38C438FC3DEDFCB57D411 | UNKNOWN_0_2010-12-14T05:00:00Z_131 | 17204 | 68638.69921875 | 0 |
| F371D4237C837D448824697EB0162905 | UNKNOWN_0_2011-01-10T05:00:00Z_198 | 0 | 85842.69921875 | 0 |
| 808496DBDE76CB4F911396BB817724F3 | UNKNOWN_0_2011-10-17T05:00:00Z_352 | 0 | 85842.69921875 | 0 |
| 433D64C871AE4E46A0E1BFCE2BB69BA7 | UNKNOWN_0_2011-11-14T05:00:00Z_364 | 0 | 85842.69921875 | 0 |
| 9545DEF1666B5F4D8626F19F8E9E9333 | UNKNOWN_0_2012-03-07T22:19:18Z_418 | 10948 | 85842.69921875 | 0 |
| 244D7D89B79E0F4E91100E4ADB300656 | UNKNOWN_0_2012-04-27T20:33:26Z_439 | 50945 | 96790.69921875 | 0 |
| 115A427BBB1D2C43BA11D9E5875FAA2C | UNKNOWN_0_2012-07-20T16:17:54Z_465 | 480049 | 147735.69921875 | 0 |
| 3A2271EFCC767E4CA40017E68802F10C | UNKNOWN_0_2012-08-01T17:26:38Z_478 | 54298 | 627784.69921875 | 0 |
| 1B9AFD2C20362F48A486E8A535B29AF5 | UNKNOWN_1_2011-12-13T17:15:53Z_20 | -113810 | 0 | 0 |
本质上-所有记录都按“类别”和“批准”字段值分组，然后按“批准日期”，“文档编号”（如果已批准= TRUE）或“发布日期”，“文档编号”（如果“批准” =
FALSE）进行排序。最后，对具有相同类别和批准值的每条先前记录的值进行逐笔记录的总和，并在单独的列中报告负数和正数。
我可以通过子选择获得所需的结果，但是性能却很差（700条记录需要6.5秒）。我一直在尝试将CASE WHEN语句与各种GROUP
BY和HAVING子句结合使用，但是我似乎无法获得正确的值（我无法获得确定“上一个”记录以满足我的要求的逻辑）
。但是，使用这种方法的性能非常好（对于相同的数据集，大多数情况下少于200ms）。
这是我可行的尝试（仅适用于SQLite，下面为T-SQL），但需要很长时间：
SELECT a.GUID,
SUM(CASE WHEN b.Value>0 THEN b.Value ELSE 0 END) as positive_previous_total,
SUM(CASE WHEN b.Value<0 THEN b.Value ELSE 0 END) as negative_previous_total
FROM Changes AS a left join Changes as b
ON b.rowid != a.rowid
AND b.Approved =a.Approved
AND b.Category=a.Category
AND ((IFNULL(SUBSTR(CASE WHEN b.Approved THEN b.ApprovedDate ELSE b.IssuedDate END, 1, 10), '0000-00-00')<IFNULL(SUBSTR(CASE WHEN a.Approved THEN a.ApprovedDate ELSE a.IssuedDate END, 1, 10), '0000-00-00')
OR ((IFNULL(SUBSTR(CASE WHEN b.Approved THEN b.ApprovedDate ELSE b.IssuedDate END, 1, 10), '0000-00-00')=IFNULL(SUBSTR(CASE WHEN a.Approved THEN a.ApprovedDate ELSE a.IssuedDate END, 1, 10), '0000-00-00') AND b.DocumentNumber<a.DocumentNumber))))
GROUP BY a.rowid
这是我可行的尝试（T-SQL）
SELECT
a.[GUID],
[positive_previous_total] = SUM(CASE WHEN b.Value>0 THEN b.Value ELSE 0 END),
[negative_previous_total] = SUM(CASE WHEN b.Value<0 THEN b.Value ELSE 0 END)
FROM
#Changes a
LEFT OUTER JOIN #Changes b
ON
b.[GUID] <> a.[GUID] AND
b.Approved = a.Approved AND
b.Category = a.Category
AND
(
ISNULL(SUBSTRING(CASE WHEN b.Approved=1 THEN b.ApprovedDate ELSE b.IssuedDate END, 1, 10), '0000-00-00')
< ISNULL(SUBSTRING(CASE WHEN a.Approved=1 THEN a.ApprovedDate ELSE a.IssuedDate END, 1, 10), '0000-00-00')
OR
(
ISNULL(SUBSTRING(CASE WHEN b.Approved=1 THEN b.ApprovedDate ELSE b.IssuedDate END, 1, 10), '0000-00-00')
=ISNULL(SUBSTRING(CASE WHEN a.Approved=1 THEN a.ApprovedDate ELSE a.IssuedDate END, 1, 10), '0000-00-00')
AND
b.DocumentNumber<a.DocumentNumber
)
)
GROUP BY a.[GUID]
有人对我获得更好的结果有任何建议吗？
在此先感谢您的帮助。
问题答案：
两三名SO重量级人物做出了贡献，因此我会与您的DBA联系，看看可以进行哪些更改。
似乎共识是您需要在changes表上添加一些索引。
我还喜欢将一些计算字段添加到该表中，以使后续脚本更具可读性的想法。
如果您能够更改某些数据类型，那么答案中会有很多建议。
类似资料：
-
问题内容： 我在Python内的多处理方面breaking之以鼻，但我没有运气将话题缠住。基本上，我有一个过程很耗时。我需要将其运行在1到100的范围内，但是一旦满足我要寻找的条件，我想中止所有进程。条件是返回值== 90。 这是一个非多进程代码块。谁能给我一个例子，说明如何将其转换为多进程函数，一旦满足“ 90”的条件，代码将退出所有进程？ 编辑： 测试第3版时出现此错误。知道是什么原因造成的吗
-
我尝试在数据帧“df_energy”中添加一个新的列“energy_class”，如果“consumpion_energy”值为 有什么办法可以帮我吗？ 先谢谢你
-
本文向大家介绍webpack打包优化的几个方法总结，包括了webpack打包优化的几个方法总结的使用技巧和注意事项，需要的朋友参考一下 为什么要优化打包？ 项目越做越大，依赖包越来越多，打包文件太大 单页面应用首页白屏时间长，用户体验差 我们的目的 减小打包后的文件大小 首页按需引入文件 优化 webpack 打包时间 优化方式 1、 按需加载 1.1 路由组件按需加载 1.2 第三方组件和插件。
-
我收集了用户在商店购买的物品，以及他从朋友那里得到的喜欢和不喜欢的东西。集合字段如下所示： 现在，我想得到以下总结： 获取用户X的（喜欢-不喜欢）差异 获取用户X的差异（喜欢-不喜欢）到存储Y 获取用户X的（喜欢-不喜欢）差异到商店Y和产品Z 对于#1，我做了： 我得到了正确的结果： [{"_id"："542ea90fbb1e37b09f660980"，"rankDiff"： 2}] 但当我试图通
-
我有一个处理大量文件的程序，其中每个文件需要做两件事：首先，读取并处理文件的一部分，然后存储生成的MyFileData。第一部分可以并行，第二部分不能并行。 按顺序做每件事都非常慢，因为CPU必须等待磁盘，然后工作一点，然后发出另一个请求，然后再次等待。。。 我做了以下事情 这很有帮助。然而，我想改进两件事： > 以固定顺序执行，而不是首先处理任何可用的结果。如何更改它？ 有数千个文件需要处理，启
-
我在Amazon DynamoDB表分区键和范围键。 表结构 现在我想使用带有多个扫描条件的C#库从表中检索数据。 HashKey=123 有什么方法可以使用c#dynamoDB API实现这一点吗？请帮忙