SQL Server 尝试使用日期之间的值JOIN事务数据表(并动态聚合

oogrdqng 于 2022-12-17 发布在其他

关注(0)|答案(2)|浏览(125)

我正在尝试编写一个查询来检查源系统（事务数据）中聚合数据的导入表。我觉得它应该很简单，但我似乎无法理解它。
聚合表（我们称之为DW）如下所示：
| 姓名ID|开始时间|结束时间|材料|共计|计数|
| - ------|- ------|- ------|- ------|- ------|- ------|
| 19测试|2022年12月4日23时15分|2022年12月5日01时57分00秒|TAM|二十六|四个|
| 19测试|2022年12月4日23时15分|2022年12月5日01时57分00秒|英国广播公司|三十二|五个|
| 20测试|2022年12月4日20时15分|2022年12月4日23时11分|TAM|五十四|三个|
| 20测试|2022年12月4日20时15分|2022年12月4日23时11分|英国广播公司|一百零六|六个|
Total是SRC表（如下）中的Amount的总和，Count是与NameID和Material匹配且发生在StartTime和EndTime之间的记录的计数。
我想检查源表，以确保获取信息的管道正确地聚合了所有内容。
源表（我们称之为SRC）如下所示：
| 姓名ID|时间戳|材料|金额|
| - ------|- ------|- ------|- ------|
| 19测试|2022年12月4日22时15分56000秒|TAM|三个|
| 19测试|2022年12月4日23时17分02秒|TAM|五个|
| 19测试|2022年12月4日23时18分06秒|TAM|七|
| 19测试|2022年12月4日23时19分14秒|TAM|十一|
| 19测试|2022年12月4日23时20分19秒|英国广播公司|1个|
| 19测试|2022年12月4日23时21分19秒|英国广播公司|六个|
| 19测试|2022年12月4日23时22分33秒|英国广播公司|九|
| 19测试|2022年12月4日23时23分36秒|计算机辅助设计|第二章|
| 19测试|2022年12月4日23时24分53.000秒|英国广播公司|六个|
| 19测试|2022年12月4日23时25分56千|TAM|三个|
| 19测试|2022年12月4日23时27分23秒|计算机辅助设计|八个|
| 19测试|2022年12月4日23时28分25秒|英国广播公司|十个|
| 20测试|2022年12月4日18时15分56千|TAM|十一|
| 20测试|2022年12月4日20时17分02秒|TAM|二十个|
| 20测试|2022年12月4日20时18分06秒|TAM|三十三|
| 20测试|2022年12月4日20时19分14秒|TAM| 1个|
| 20测试|2022年12月4日20时20分19时00分|英国广播公司|七|
| 20测试|2022年12月4日20时21分19秒|英国广播公司|二十二|
| 20测试|2022年12月4日20时22分33秒|英国广播公司|三十一|
| 20测试|2022年12月4日20时23分36秒|计算机辅助设计|七|
| 20测试|2022年12月4日20时24分53.000秒|英国广播公司|十一|
| 20测试|2022年12月4日20时25分56千|英国广播公司|二十一|
| 20测试|2022年12月4日20时27分23秒|计算机辅助设计|二十九|
| 20测试|2022年12月4日20时28分25秒|英国广播公司|十四|

我包括了2条不应匹配的时间戳记录和4条不匹配的材料记录

理想情况下，我寻找的结果集是DW表的扩展，它的列表示SRC表中的总计：
| 姓名ID|开始时间|结束时间|材料|共计|计数|共计- SRC|计数- SRC|
| - ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|
| 19测试|2022年12月4日23时15分|2022年12月5日01时57分00秒|TAM|二十六|四个|二十六|四个|
| 19测试|2022年12月4日23时15分|2022年12月5日01时57分00秒|英国广播公司|三十二|五个|三十二|五个|
| 20测试|2022年12月4日20时15分|2022年12月4日23时11分|TAM|五十四|三个|五十四|三个|
| 20测试|2022年12月4日20时15分|2022年12月4日23时11分|英国广播公司|一百零六|六个|一百零六|六个|
我试过下面这样的东西：

SELECT dw.NameID, dw.StartTime, dw.EndTime, dw.Material, dw.Total, dw.Count, src.Total, src.Count
FROM dw
INNER JOIN (
    SELECT SUM(src.Amount) Total, count(*) count, src.NameID, src.Material
    FROM src
    WHERE src.NameID = dw.NameID
    AND src.SampleDateTime BETWEEN dw.StartTime AND dw.EndTime
    GROUP BY src.NameID, d.Material
) as d on dw.NameID = d.NameID

但我得到了以下错误：
消息4104，级别16，状态1，第80行
无法绑定多部分标识符“dw.Machine”。
我曾经考虑过使用游标和临时表来循环并基于DW表中的记录聚合SRC表中的数据，但我认为有一种更有效（也更简单）的方法可以做到这一点。

sql-server

来源：https://stackoverflow.com/questions/74755735/trying-to-join-table-of-transactional-data-and-aggregate-on-the-fly-using-betw

2条答案

按热度按时间

dgiusagp1#

我认为你绝对是在正确的道路上，你只是过于复杂了。
不要使用子查询，而只使用普通的JOIN。然后对分组进行一些小的更新，并修复一些列名和连接：

SELECT dw.NameID, dw.StartTime, dw.EndTime, dw.Material, dw.Total, dw.Count, 
       SUM(src.Amount) AS Total_SRC, count(*) AS Count_SRC
FROM   dw
       INNER JOIN src ON dw.NameID = src.NameID
                   AND dw.Material = src.Material 
                   AND src.SampleDateTime BETWEEN dw.StartTime AND dw.EndTime
GROUP BY dw.NameID, dw.StartTime, dw.EndTime, dw.Material, dw.Total, dw.Count

结果如下所示，可参见this db<>fiddle

NameID  StartTime                EndTime                  Material  Total  Count  Total_SRC  Count_SRC
19Test  2022-12-04 23:15:00.000  2022-12-05 01:57:00.000  BVO       32     5      32         5
19Test  2022-12-04 23:15:00.000  2022-12-05 01:57:00.000  TAM       26     4      26         4
20Test  2022-12-04 20:15:00.000  2022-12-04 23:11:00.000  BVO       106    6      106        6
20Test  2022-12-04 20:15:00.000  2022-12-04 23:11:00.000  TAM       54     3      54         3

注意--如果您需要频繁地运行这个命令和/或想要快速得到结果，我会查看src表上的索引。

将聚集索引按顺序放置在Name_Id、Material、SampleDateTime上
在这些字段上放置一个非聚集索引，并INCLUDEAmount（尽管这基本上创建了整个表的副本）

赞(0）回复(0）举报 2022-12-17

kxkpmulp2#

换一种思路，因为您希望 * 根据源系统表检查导入的聚合数据表 *，所以可以按如下方式使用EXCEPT运算符：
SQL EXCEPT子句/运算符用于合并两个SELECT语句，并从第一个SELECT语句返回第二个SELECT语句未返回的行。这意味着EXCEPT仅返回第二个SELECT语句中不可用的行。

Select NameID,
       StartTime,
       EndTime,
       Material,
       Total,
       [Count]
From DW
Except
Select S.NameID, 
       D.StartTime,
       D.EndTime,
       S.Material,
       Sum(S.Amount) As Total,
       Count(*) As [Count]
From SRC S Join DW D
On S.NameID = D.NameID And S.Material  = D.Material
   And S.TimeStamp Between D.StartTime And D.EndTime
Group By S.NameID, S.Material, D.StartTime, D.EndTime

因此，当在源表的聚合数据中找到所有导入的表行时，* 此查询不会返回任何行 *，这意味着导入的表聚合正确。
参见demo。

赞(0）回复(0）举报 2022-12-17

我来回答

SQL Server 尝试使用日期之间的值JOIN事务数据表(并动态聚合

2条答案

相关问题

热门标签

最新问答