SQL查询中间过程步骤

Question

我有一个事实表，每行代表这些关键信息

• 序列号
• 活动类型（运输，返回，索赔）
• 活动日期
• 帐户名

串行一生中可以有许多事件。例如

1. 运送到 _
2. 从 _
返回
3. 运送到 _
4. _
的主张
5. 从 _
返回
6. _
的主张
7. 运送到 _
8. _
的主张

这里的挑战是我需要检查每个实例是否是基于先前事务的有效交易。例如：

• 仅当匹配最后发货帐户
时，回报才有效
• 索赔仅在尚未返回时有效，并且索赔与最后发货帐户匹配。

i 到目前为止，已经能够做到的是创建报告，因为该串行的最新船舶事件。本质上忽略了其他一切。

我无法弄清楚的是，我将如何创建一份全年报告，以捕捉一年中的所有可能违规行为。请记住，我需要成千上万的序列，我需要运行此报告。

换句话说，使用上面的示例，我可以轻松地将＃8识别为有效/无效，但是我将如何创建一个报告，该报告也说＃4和＃6的状态？然后为每个系列执行此操作。我敢肯定，这种类型的问题经常出现，但是我无法找到类似问题的资源。

Answer 1

我喜欢这个有趣的，听起来像我对企业用户的要求，这就是我的做法。要执行类似规则，我使用了LEAD和LAG分析功能。以下是演示如何使用它们。只需弄清楚什么是规则，然后使用LAG或LEAD

写出它

铅-http：//technet.microsoft.com/en-us/library/hh213125.aspx

lag -http：//technet.microsoft.com/en-us/library/hh231256.aspx

DECLARE @MyTable AS TABLE
    (
     SerialNumber BIGINT
    ,ActivityType VARCHAR(25)
    ,ActivityDate DATETIME
    ,AccountName VARCHAR(100)
    )
INSERT INTO @MyTable VALUES(1,'SHIP','01/01/2013','Bill');
INSERT INTO @MyTable VALUES(2,'RETURN','01/02/2013','Bill');
INSERT INTO @MyTable VALUES(3,'SHIP','01/03/2013','Bill');
INSERT INTO @MyTable VALUES(4,'RETURN','01/01/2013','Joe');
INSERT INTO @MyTable VALUES(5,'SHIP','01/02/2013','Joe');
INSERT INTO @MyTable VALUES(6,'SHIP','01/01/2013','James');
INSERT INTO @MyTable VALUES(7,'SHIP','01/02/2013','James');
INSERT INTO @MyTable VALUES(8,'SHIP','01/02/2013','Bill');
SELECT *
,CASE WHEN LAG(ActivityType) OVER (PARTITION BY AccountName ORDER BY ActivityDate ASC) IS NULL AND ActivityType IN ('Return','Claim') THEN 'FAIL' ELSE 'PASS' END AS Rule1
,CASE WHEN LAG(ActivityType) OVER (PARTITION BY AccountName ORDER BY ActivityDate ASC) IN ('Return','Claim') AND ActivityType IN ('Return','Claim') THEN 'FAIL' ELSE 'PASS' END AS Rule2
,CASE WHEN LAG(ActivityType) OVER (PARTITION BY AccountName ORDER BY ActivityDate ASC) = 'SHIP' AND ActivityType IN ('Return','Claim') THEN 'PASS' ELSE 'FAIL' END AS Rule3
,CASE WHEN LAG(ActivityType) OVER (PARTITION BY AccountName ORDER BY ActivityDate ASC) = 'SHIP' AND ActivityType = 'SHIP' THEN 'FAIL' ELSE 'PASS' END AS Rule4
FROM @MyTable
ORDER BY AccountName, ActivityDate

规则1检查以查看该返回和索赔仅在船舶之后发生，如果是第一个发生规则失败的项目。

规则2检查以确保互相回报和索赔没有发生。

规则3更多是验证良好的记录，其中返回和索赔以正确的顺序发生。

规则4是防止两次运送到同一帐户。

Answer 2

这是解决问题的逻辑。

您将附加到每个记录的最后一个发货帐户和最后一个返回的帐户。这些是逻辑所需的值。

得到这些有点复杂。首先，查询将1分配给每个发货/返回的行。然后，它做了一个累积的总和，以获取具有相同"最近发货/返回"的行。然后在行内，将分配帐户。

之后，逻辑应该很容易。这里是：

select f.*
from (select f.*, max(AccountName) over (partition by Serial, ShippedGroup) as LastShippedAccount,
             max(AccountName) over (partition by Serial, ReturnedGroup) as LastReturnedGroup
      from (select f.*,
                   sum(case when ActivityName = 'Shipped' then 1 else 0 end) over (partition by Serial
                                                                                   order by ActivityDate
                                                                                  ) as ShippedGroup,
                   sum(case when ActivityName = 'Returned' then 1 else 0 end) over (partition by Serial
                                                                                  order by ActivityDate
                                                                                 ) as ReturnedGroup,
            from fact f
           ) f
     ) f
where not (ActivityName = 'Return' and ActivityAccount = LastShippedAccount) or
      not (ActivityName = 'Claim' and ActivityAccount = LastShippedAccount and ActivityAccount <> LastReturnedAccount);