比较表中的 3 个连续行 [英] Compare 3 Consecutive rows in a table

问题描述

我有一个有趣的问题.我有一个员工表如下

Hi I have an interesting problem. I Have an Employee Table AS Follows

CREATE TABLE EMPLOYEE(
EMPLOYEE_ID INTEGER,
SALARY DECIMAL(18,2),
PAY_PERIOD DATE)

现在这些表有员工，他们中的一些人每月、一些每周、一些双周和一些每天获得报酬.我们想要的是找到一个指标，如果三个连续支付期的工资相等，则显示Y".让我们以下面的例子为例.

Now the tables have employees some of whom get paid monthly,some weekly, some biweekly and some daily. What we want is to find an Indicator saying 'Y' if the salary of three consecutive Pay Periods is equal. Lets take the following example.

Employee   Pay_Period     Salary

  1         01/01/2012    $500
  1         08/01/2012    $200
  1         15/01/2012    $200
  1         22/01/2012    $200
  1         29/01/2012    $700

在这种情况下，指标应为是，因为连续 3 个支付期的工资为 200 美元.

In this case the indicator should be Yes because 3 consecutive pay periods have a salary of $200.

由于支付周期的数量不是恒定的，我不确定如何编写此代码，因为我事先不知道我需要多少个左连接.由于我在 Teradata 中编写此代码，因此我尝试使用 RECURSIVE 函数但被难住了.关于如何进行此操作的任何一般想法?我宁愿不创建存储过程或具有 PL/SQL 逻辑.

Since the number of pay periods is not constant I am unsure of how to write this code because I do not know from before hand how many left joins I will need.Since I am writing this in Teradata I tried using the RECURSIVE Function but got stumped. Any general ideas on how to proceed with this?I would prefer not creating a stored procedure or having PL/SQL logic.

推荐答案

Teradata 可能不像 Oracle 和 SQL Server 现在支持的那样支持 LEAD 和 LAG，但这些功能的前提是为您选择正确的窗口窗口聚合函数.在 Teradata 中，可以通过使用窗口聚合函数中的 ROWS BETWEEN 子句来实现 LEAD 和 LAG.

Teradata may not support LEAD and LAG in the manner that Oracle and SQL Server now support it but the premise of these functions is based on selecting the correct window for your Window Aggregate functions. In Teradata LEAD and LAG can be accomplished by using the ROWS BETWEEN clause in your Window Aggregate Function.

您可以通过以下方式使用 ROWS BETWEEN 和桌上的单次传递来完成您想要做的事情:

Here is how you can accomplish what you are looking to do using ROWS BETWEEN and a single pass at the table:

CREATE VOLATILE TABLE myTable
( myID SMALLINT NOT NULL,
  PayPeriod DATE NOT NULL,
  PayAmount DECIMAL(5,2) NOT NULL)
PRIMARY INDEX (myID) 
ON COMMIT PRESERVE ROWS;

INSERT INTO myTable VALUES (1, DATE '2012-01-01', 500);
INSERT INTO myTable VALUES (1, DATE '2012-01-08', 200);
INSERT INTO myTable VALUES (1, DATE '2012-01-15', 200);
INSERT INTO myTable VALUES (1, DATE '2012-01-22', 200);
INSERT INTO myTable VALUES (1, DATE '2012-01-29', 700);


SELECT myID
     , PayPeriod
     , PayAmount
     , MAX(PayAmount) OVER (PARTITION BY myID 
                                ORDER BY PayPeriod 
                            ROWS BETWEEN 1 FOLLOWING 
                                     AND 1 FOLLOWING) AS NextPayAmount_
     , MAX(PayAmount) OVER (PARTITION BY myID 
                                ORDER BY PayPeriod 
                            ROWS BETWEEN 2 FOLLOWING 
                                     AND 2 FOLLOWING) AS NextPayAmount2_
     , CASE WHEN NextPayAmount_ = PayAmount
             AND NextPayAmount2_ = PayAmount
            THEN 'Y'
            ELSE 'N'
       END PayIndicator_
  FROM myTable;

结果

1   2012-01-01  500 200 200 N
1   2012-01-08  200 200 200 Y
1   2012-01-15  200 200 700 N
1   2012-01-22  200 700   ? N
1   2012-01-29  700   ?   ? N

这篇关于比较表中的 3 个连续行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！