问题描述
我将数据导入到 MSAccess 的临时表中，如下所示:
I have data imported into a temporary table in MSAccess which looks like this:
我添加了我需要使用 SQL 查询计算的差距"和上一个/当前"列.差距阈值"是用户输入或 PARAMETER 提供给查询和例如是 300.GlobalID 将 ItemID 分组，而每个 ItemID 都是唯一编号.
to which I have added the "Gap" and "Previous/Current" columns that I need to calculate using an SQL Query. The "Gap Threshold" is User input or PARAMETER supplied to Query and for e.g. is 300. The GlobalID groups ItemID's whereas each ItemID is unique number.
我想做的是计算 GAP
What i want to do is calculate the GAP
(GAP = TEMPORARY_1！[版本日期] - 临时！[版本日期])
在相似 GlobalID 的 ItemID 之间，并标识具有 GAP > GAP THRESHOLD 值的项目.基于这个 GAP，对于每个 GlobalID 分组的 ItemID，我想确定哪个是上一个"ItemID，哪个是当前"ItemID.
between ItemID's of similar GlobalID's and identify the items having GAP > GAP THRESHOLD value. Based on this GAP, for each GlobalID-grouped ItemID's, I want to determine which is the "Previous" ItemID and which is the "Current" ItemID.
即确定哪个是上一个项目，哪个是当前项目，两者之间的差距超过 300 天.
最后，创建另一个表，它只会为每个 GlobalID 导入这些 Current/Previous Pairs，但将它们显示为一个记录，如下所示:
Finally, CREATE ANOTHER TABLE that will only import these Current/Previous Pairs for each GlobalID, but display them as one record each like this:
或者在计算 GAP > GAP THRESHOLD 之后创建 2 个单独的表，称为 tblPrevious 和amp; 是更好的设计吗?tblCurrent 来自 临时表 像这样吗?:
OR Is it a better design to Create 2 separate Tables AFTER CALCULATING GAP > GAP THRESHOLD, called tblPrevious & tblCurrent from the Temporary table like this?:
我需要有人为我指明正确的方向，以进行更好的规范化设计并使用 SQL 查询实现这一点.注意:所有的表都需要每次根据导入的新数据提取动态生成.
I need someone to point me in the right direction to have a better normalized design and achieve this using SQL query. Note: all the tables need to be generated dynamically everytime based on new data extract that is imported.
下面的查询在 Gap 列上给出错误并且不计算上一个/当前:
The below query gives error on Gap column and doesn't calculate Previous/Current:
PARAMETERS Threshold Long;
SELECT TEMPORARY.GlobalID, TEMPORARY.ItemID, TEMPORARY.[Version Date], IIf([TEMPORARY]![GlobalID]=
[TEMPORARY_1]![GlobalID],Max([TEMPORARY]![Version Date])-Min([TEMPORARY_1]![Version Date])=0,"Previous") AS Previous, TEMPORARY_1.ItemID, TEMPORARY_1.[Version Date], IIf([TEMPORARY]![GlobalID]=[TEMPORARY_1]![GlobalID],Max([TEMPORARY]![Version Date])-Min([TEMPORARY_1]![Version Date])>[Threshold],"Current") AS [Current], IIf(([TEMPORARY]![Version Date]-[TEMPORARY_1]![Version Date])>[Threshold],[TEMPORARY]![Version Date]-[TEMPORARY_1]![Version Date],"") AS GAP
FROM TEMPORARY, TEMPORARY AS TEMPORARY_1
GROUP BY TEMPORARY.GlobalID, TEMPORARY.ItemID, TEMPORARY.[Version Date], TEMPORARY_1.GlobalID, TEMPORARY_1.ItemID, TEMPORARY_1.[Version Date];
任何帮助将不胜感激.
推荐答案
Review Allen Browne Subquery.
叙述中描述的要求与标题不同.以下是对两者的建议.
Requirements described in narrative differ from the title. Here are suggestions for both.
查询拉取当前/以前的货币对.
Queries pulling Current/Previous pairs.
查询 1:
SELECT [GlobalID], [ItemID] AS CurItemID, [Version Date] AS CurDate, (SELECT TOP 1 [Version Date] FROM Temporary AS Dupe WHERE Dupe.GlobalID=Temporary.GlobalID AND Dupe.ItemID < Temporary.ItemIDORDER BY Dupe.GlobalID, Dupe.ItemID DESC) AS PreDate, (SELECT TOP 1 [ItemID] FROM Temporary AS Dupe WHERE Dupe.GlobalID=Temporary.GlobalID AND Dupe.ItemID < Temporary.ItemID ORDER BY Dupe.GlobalID, Dupe.ItemID DESC) AS PreItemIDFROM [临时];
查询 2:
SELECT Query1.GlobalID, Query1.CurItemID, Query1.CurDate, Query1.PreDate, Query1.PreItemID, DateDiff("d",[PreDate],[CurDate]) AS Gap FROM Query1 WHERE ((([GlobalID] & [CurItemID]) 在 (SELECT TOP 1 GlobalID & CurItemID FROM Query1 AS Dupe WHERE Dupe.GlobalID = Query1.GlobalID ORDER BY GlobalID, CurItemID DESC))) AND DateDiff("d",[PreDate],[CurDate])> Int([输入阈值]);
最终输出:
GlobalID CurItemID CurDate PreDate PreItemID 差距
00109086 2755630 2/26/2015 3/11/2014 2130881 352
00114899 2785590 3/13/2015 3/25/2014 2093191 353
00154635 2755623 2/26/2015 4/4/2014 2176453 328
以下查询解决了标题中所述的最小值/最大值要求.不像当前/以前的查询那么慢，但如果数据集变得更大，我预计它会变得非常慢.
Here is query that addresses the requirement for Minimum/Maximum as stated in your title. Not as slow as the Current/Previous queries but if dataset gets significantly larger I expect it will get very slow.
SELECT Maximum.GlobalID, Maximum.ItemID AS MaxItem, Maximum.[Version Date] AS MaxItemDate, Minimum.ItemID AS MinItem, Minimum.[Version Date] AS MinItemDate, Maximum.[Version Date]-Minimum.[Version Date] AS Gap
FROM
(SELECT T1.GlobalID, T1.ItemID, T1.[Version Date] FROM [Temporary] AS T1 WHERE (((T1.ItemID) In (SELECT Min([ItemID]) AS MinItem FROM Temporary GROUP BY GlobalID)))) AS Minimum
INNER JOIN
(SELECT T1.GlobalID, T1.ItemID, T1.[Version Date] FROM [Temporary] AS T1 WHERE (((T1.ItemID) In (SELECT Max([ItemID]) AS MaxItem FROM Temporary GROUP BY GlobalID)))) AS Maximum
ON Minimum.GlobalID = Maximum.GlobalID
WHERE Maximum.[Version Date]-Minimum.[Version Date]>Int([Enter Threshold]);
此外，您的日期采用国际格式.如果您遇到问题，请查看 Allen Browne International Dates
Also, your dates are in international format. If you encounter issues with that, review Allen Browne International Dates
这篇关于查询从同一表中的另一条记录中获取值并按大于间隙阈值的差异进行过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！