搜索
搜索

减少由于错误而在SQL表中创建的数据

Namoshek 发表于 Dev
11
那摩舍克

不幸的是，由于软件错误在开发环境中不够明显，无法被识别，因此发生了我们创建大量实际上不需要的SQL记录的情况。记录不会损害数据完整性或其他任何东西，但是根本没有必要。

我们正在研究如下数据库模式：

entity_static (just some static data that won't change):

id | val1 | val2 | val3
-----------------------
1  | 50   | 183  | 93
2  | 60   | 823  | 123


entity_dynamic (some dynamic data we need a historical record of):

id | entity_static_id | val1 | val2 | valid_from          | valid_to
-------------------------------------------------------------------------------
1  | 1                | 50   | 75   | 2018-01-01 00:00:00 | 2018-01-01 00:59:59
2  | 1                | 50   | 75   | 2018-01-01 01:00:00 | 2018-01-01 01:59:59
3  | 1                | 50   | 75   | 2018-01-01 02:00:00 | 2018-01-01 02:59:59
4  | 1                | 50   | 75   | 2018-01-01 03:00:00 | 2018-01-01 03:59:59
5  | 2                | 60   | 75   | 2018-01-01 00:00:00 | 2018-01-01 00:59:59
6  | 2                | 60   | 75   | 2018-01-01 01:00:00 | 2018-01-01 01:59:59
7  | 2                | 60   | 75   | 2018-01-01 02:00:00 | 2018-01-01 02:59:59
8  | 2                | 60   | 75   | 2018-01-01 03:00:00 | 2018-01-01 03:59:59

除了val1和以外，还有更多列val2，这只是一个示例。

该entity_dynamic表描述了在给定时间内有效的参数。它不是某个时间点的记录（例如传感器数据）。

因此，可以将所有相等的记录轻松地汇总为一条记录，如下所示：

id | entity_static_id | val1 | val2 | valid_from          | valid_to
-------------------------------------------------------------------------------
1  | 1                | 50   | 75   | 2018-01-01 00:00:00 | 2018-01-01 03:59:59
5  | 2                | 60   | 75   | 2018-01-01 00:00:00 | 2018-01-01 03:59:59

valid_to列中的数据可能为NULL。

我的问题是，现在可以使用哪些查询将具有连续有效范围的相似记录聚合到一个记录中。分组应使用上的外键完成entity_static_id。

索伦·孔斯塔德 
with entity_dynamic  as
(
select
*
from 
(values
('1','1','50','75',' 2018-01-01 00:00:00 ',' 2018-01-01 00:59:59')
,('2','1','50','75',' 2018-01-01 01:00:00 ',' 2018-01-01 01:59:59')
,('3','1','50','75',' 2018-01-01 02:00:00 ',' 2018-01-01 02:59:59')
,('4','1','50','75',' 2018-01-01 03:00:00 ',' 2018-01-01 03:59:59')
,('5','2','60','75',' 2018-01-01 00:00:00 ',' 2018-01-01 00:59:59')
,('6','2','60','75',' 2018-01-01 01:00:00 ',' 2018-01-01 01:59:59')
,('7','2','60','75',' 2018-01-01 02:00:00 ',' 2018-01-01 02:59:59')
,('8','2','60','75',' 2018-01-01 03:00:00 ',' 2018-01-01 03:59:59')
,('9','1','60','75',' 2018-01-01 04:00:00 ',' 2018-01-01 04:59:59')
,('10','1','60','75',' 2018-01-01 05:00:00 ',' 2018-01-01 05:59:59')
,('11','2','70','75',' 2018-01-01 04:00:00 ',' 2018-01-01 04:59:59')
,('12','2','70','75',' 2018-01-01 05:00:00 ',' 2018-01-01 05:59:59')
,('13','2','60','75',' 2018-01-01 06:00:00 ',' 2018-01-01 06:59:59')
)
 a(id , entity_static_id , val1 , val2 , valid_from , valid_to)
 )
 ,

首先，为每个entity_static_id（唯一组）添加val1和val2的唯一组合的行号，然后为entity_static_id添加行号。由valid_from降序排列

 step1 as
 (
 select 
 id , entity_static_id , val1 , val2 , valid_from , valid_to
 ,row_number() over (partition by entity_static_id,val1,val2 order by valid_from) valrn
 ,ROW_NUMBER() over (partition by entity_static_id order by valid_from desc) rn

 from entity_dynamic 
)

这给出：

+----------------------------------------------------------------------------------------+
|id|entity_static_id|val1|val2|valid_from           |valid_to            |unique_group|rn|
+----------------------------------------------------------------------------------------+
|10|1               |60  |75  | 2018-01-01 05:00:00 | 2018-01-01 05:59:59|2           |1 |
|9 |1               |60  |75  | 2018-01-01 04:00:00 | 2018-01-01 04:59:59|1           |2 |
|4 |1               |50  |75  | 2018-01-01 03:00:00 | 2018-01-01 03:59:59|4           |3 |
|3 |1               |50  |75  | 2018-01-01 02:00:00 | 2018-01-01 02:59:59|3           |4 |
|2 |1               |50  |75  | 2018-01-01 01:00:00 | 2018-01-01 01:59:59|2           |5 |
|1 |1               |50  |75  | 2018-01-01 00:00:00 | 2018-01-01 00:59:59|1           |6 |
|13|2               |60  |75  | 2018-01-01 06:00:00 | 2018-01-01 06:59:59|5           |1 |
|12|2               |70  |75  | 2018-01-01 05:00:00 | 2018-01-01 05:59:59|2           |2 |
|11|2               |70  |75  | 2018-01-01 04:00:00 | 2018-01-01 04:59:59|1           |3 |
|8 |2               |60  |75  | 2018-01-01 03:00:00 | 2018-01-01 03:59:59|4           |4 |
|7 |2               |60  |75  | 2018-01-01 02:00:00 | 2018-01-01 02:59:59|3           |5 |
|6 |2               |60  |75  | 2018-01-01 01:00:00 | 2018-01-01 01:59:59|2           |6 |
|5 |2               |60  |75  | 2018-01-01 00:00:00 | 2018-01-01 00:59:59|1           |7 |
+----------------------------------------------------------------------------------------+

步骤2是将每个唯一组的行号与总行num相加，因为最后一个行是降序的，因此具有相等值的行彼此相邻vil具有相同的总和，在此示例中称为tar

,step2 as
(
select
*
,unique_group+rn tar
from step1
)

步骤2给出：

+--------------------------------------------------------------------------------------------+
|id|entity_static_id|val1|val2|valid_from           |valid_to            |unique_group|rn|tar|
+--------------------------------------------------------------------------------------------+
|10|1               |60  |75  | 2018-01-01 05:00:00 | 2018-01-01 05:59:59|2           |1 |3  |
|9 |1               |60  |75  | 2018-01-01 04:00:00 | 2018-01-01 04:59:59|1           |2 |3  |
|4 |1               |50  |75  | 2018-01-01 03:00:00 | 2018-01-01 03:59:59|4           |3 |7  |
|3 |1               |50  |75  | 2018-01-01 02:00:00 | 2018-01-01 02:59:59|3           |4 |7  |
|2 |1               |50  |75  | 2018-01-01 01:00:00 | 2018-01-01 01:59:59|2           |5 |7  |
|1 |1               |50  |75  | 2018-01-01 00:00:00 | 2018-01-01 00:59:59|1           |6 |7  |
|13|2               |60  |75  | 2018-01-01 06:00:00 | 2018-01-01 06:59:59|5           |1 |6  |
|12|2               |70  |75  | 2018-01-01 05:00:00 | 2018-01-01 05:59:59|2           |2 |4  |
|11|2               |70  |75  | 2018-01-01 04:00:00 | 2018-01-01 04:59:59|1           |3 |4  |
|8 |2               |60  |75  | 2018-01-01 03:00:00 | 2018-01-01 03:59:59|4           |4 |8  |
|7 |2               |60  |75  | 2018-01-01 02:00:00 | 2018-01-01 02:59:59|3           |5 |8  |
|6 |2               |60  |75  | 2018-01-01 01:00:00 | 2018-01-01 01:59:59|2           |6 |8  |
|5 |2               |60  |75  | 2018-01-01 00:00:00 | 2018-01-01 00:59:59|1           |7 |8  |
+--------------------------------------------------------------------------------------------+

最后，您可以使用min和maxm并根据正确的值进行分组来找到有效日期和有效日期。

select
min(id) id
,entity_static_id
,val1
,val2
,min(valid_from) valid_from
,max(valid_to) valid_to
from step2
group by entity_static_id,val1
    ,val2   
    ,tar
order by entity_static_id,valid_from

总的来说，代码是：

with entity_dynamic  as
(
select
*
from 
(values
('1','1','50','75',' 2018-01-01 00:00:00 ',' 2018-01-01 00:59:59')
,('2','1','50','75',' 2018-01-01 01:00:00 ',' 2018-01-01 01:59:59')
,('3','1','50','75',' 2018-01-01 02:00:00 ',' 2018-01-01 02:59:59')
,('4','1','50','75',' 2018-01-01 03:00:00 ',' 2018-01-01 03:59:59')
,('5','2','60','75',' 2018-01-01 00:00:00 ',' 2018-01-01 00:59:59')
,('6','2','60','75',' 2018-01-01 01:00:00 ',' 2018-01-01 01:59:59')
,('7','2','60','75',' 2018-01-01 02:00:00 ',' 2018-01-01 02:59:59')
,('8','2','60','75',' 2018-01-01 03:00:00 ',' 2018-01-01 03:59:59')
,('9','1','60','75',' 2018-01-01 04:00:00 ',' 2018-01-01 04:59:59')
,('10','1','60','75',' 2018-01-01 05:00:00 ',' 2018-01-01 05:59:59')
,('11','2','70','75',' 2018-01-01 04:00:00 ',' 2018-01-01 04:59:59')
,('12','2','70','75',' 2018-01-01 05:00:00 ',' 2018-01-01 05:59:59')
,('13','2','60','75',' 2018-01-01 06:00:00 ',' 2018-01-01 06:59:59')
)
 a(id , entity_static_id , val1 , val2 , valid_from , valid_to)
 )
 ,step1 as
 (
 select 
 id , entity_static_id , val1 , val2 , valid_from , valid_to
 ,row_number() over (partition by entity_static_id,val1,val2 order by valid_from) unique_group
 ,ROW_NUMBER() over (partition by entity_static_id order by valid_from desc) rn

 from entity_dynamic 
)
,step2 as
(
select
*
,dense_rank() over (partition by entity_static_id order by unique_group) f
,unique_group+rn tar
from step1
)
select
min(id) id
,entity_static_id
,val1
,val2
,min(valid_from) valid_from
,max(valid_to) valid_to
from step2
group by entity_static_id,val1
    ,val2   
    ,tar
order by entity_static_id,valid_from

结果是

 +------------------------------------------------------------------------+
|id|entity_static_id|val1|val2|valid_from           |valid_to            |
+------------------------------------------------------------------------+
|1 |1               |50  |75  | 2018-01-01 00:00:00 | 2018-01-01 03:59:59|
|10|1               |60  |75  | 2018-01-01 04:00:00 | 2018-01-01 05:59:59|
|5 |2               |60  |75  | 2018-01-01 00:00:00 | 2018-01-01 03:59:59|
|11|2               |70  |75  | 2018-01-01 04:00:00 | 2018-01-01 05:59:59|
|13|2               |60  |75  | 2018-01-01 06:00:00 | 2018-01-01 06:59:59|
+------------------------------------------------------------------------+

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章

由于语法错误，我无法创建SQL表

在数据库中创建表时导致SQL语法错误的原因

SQL表创建错误

我在 JQuery 数据表上有错误。JQuery 数据表在本地环境中运行良好，而在托管环境中，它给出错误

由于crashlytics build.gradle错误而在Android模拟器中运行Flutter应用

从SQL表在SQL中创建数据透视图

SQL在表A中找到，而在查询B中找不到

SQL 减少连接或 where 中的数据

在MySQL中创建SQL表时遇到错误

从SQL Server中的子查询创建临时表导致错误

在 MariaDB 中创建表时出现 SQL 语法错误

在 Workbench 中创建 SQL 表时，出现错误

在SQL中创建表

在T-SQL中创建复杂的数据透视表

在Spring Boot中创建表并从.sql文件导入数据

在单个.sql脚本中创建数据库和表

使用数据框的内容在R中创建SQL结构表

在SQL Server中创建不包含数据的表的克隆副本

创建用于查看多个表中数据的SQL View

导入SQL表时由于排序规则而出现错误

SSRS Microsoft Reporting Services-由于用户权限而在报表创建过程中可能出现问题？

由于无法在执行mainloop之前填充变量而在tkinter GUI中请求模块MissingSchema错误：如何解决此问题？

POST请求由于CORS错误而在react axios中被阻止

由于前键约束，无法在mysql中创建表

从表中创建散点图数据

如何从一个表中选择在另一表中由于某种条件而在另一表中不存在的所有记录？

如何减少DT数据表中的行高

如何减少primefaces中的数据表行高

减少数据表中的行高/边距？

TOP 榜单

  1. 1

    UITableView的项目向下滚动后更改颜色，然后快速备份

  2. 2

    Linux的官方Adobe Flash存储库是否已过时？

  3. 3

    用日期数据透视表和日期顺序查询

  4. 4

    应用发明者仅从列表中选择一个随机项一次

  5. 5

    Mac OS X更新后的GRUB 2问题

  6. 6

    验证REST API参数

  7. 7

    Java Eclipse中的错误13，如何解决？

  8. 8

    带有错误“ where”条件的查询如何返回结果？

  9. 9

    ggplot：对齐多个分面图-所有大小不同的分面

  10. 10

    尝试反复更改屏幕上按钮的位置 - kotlin android studio

  11. 11

    如何从视图一次更新多行（ASP.NET - Core）

  12. 12

    计算数据帧中每行的NA

  13. 13

    蓝屏死机没有修复解决方案

  14. 14

    在 Python 2.7 中。如何从文件中读取特定文本并分配给变量

  15. 15

    离子动态工具栏背景色

  16. 16

    VB.net将2条特定行导出到DataGridView

  17. 17

    通过 Git 在运行 Jenkins 作业时获取 ClassNotFoundException

  18. 18

    在Windows 7中无法删除文件（2）

  19. 19

    python中的boto3文件上传

  20. 20

    当我尝试下载 StanfordNLP en 模型时，出现错误

  21. 21

    Node.js中未捕获的异常错误，发生调用

热门标签

归档