Compare 3 Consecutive Rows in a Table

Compare 3 Consecutive rows in a table

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.

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;

Results

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

Compare data in consecutive rows

In SQL Server 2012+, you would use lag():

insert into othertable(col1 . . . )
    select t.*
    from (select t.*, lag(value) over (order by timestamp) as prev_value
          from table t
         ) t
    where value < prev_value;

For performance, you want an index on table(timestamp, value).

In earlier versions of SQL Server, you can use a correlated subquery or cross apply.

If you are doing this on a regular basis, such as every night, then you will want a where clause. Be careful about boundary conditions (if the value goes down just over midnight, you still want to catch that).

Postgresql compare consecutive rows and insert identical row if there are no values

One method uses generate_series() and lead():

with tt as (
      select product_id, total_revenue, timestamp,
             lead(timestamp) over (partition by product_id order by timestamp) as next_timestamp
      from t
     )
select tt.product_id, coalesce(gs.ts, tt.timestamp),
       tt.total_revenue
from tt left join lateral
     generate_series(timestamp, next_timestamp - interval '15 minute', interval '15 minute') gs(ts);

Note: My guess is that you also want this extended to the most recent timestamp in the table:

with tt as (
      select product_id, total_revenue, timestamp,
             lead(timestamp) over (partition by product_id order by timestamp) as next_timestamp,
             max(timestamp) over () as max_timestamp
      from t
     )
select tt.product_id, coalesce(gs.ts, tt.timestamp),
       tt.total_revenue
from tt left join lateral
     generate_series(timestamp,
                     coalesce(next_timestamp - interval '15 minute', max_timestamp),
                     interval '15 minute'
                    ) gs(ts);

Also, if the timestamps are not exactly at 15 minute intervals, then I would suggest that you ask a new question with explanation and more realistic sample data.

Impala compare consecutive rows and insert identical row if there are no values

I did it!

with crossed as
(
select
product_id,id_month,
rank() over (partition by product_id order by id_month asc) as r
from
(
select distinct cast(id_month as string) as id_month
from calendar d
where day_data <= date_sub(now(), interval 1 month)
) a
cross join
(select product_id, min(concat(year,month)) as minimum
from revenue
group by product_id
) b
where a.id_month >= b.minimum
)
, created as
(
select
coalesce(a.product_id,b.product_id) as product_id,
coalesce(concat(a.year,a.month),b.id_month) as id_month,
a.total_revenue,
b.r
from revenue a
full outer join crossed b
on a.product_id=b.product_id and concat(a.year,a.month)=b.id_month
where a.year is null
)
,
real as
(
select
coalesce(a.product_id,b.product_id) as product_id,
coalesce(concat(a.year,a.month),b.id_month) as id_month,
a.total_revenue,
b.r
from revenue a
full outer join crossed b
on a.product_id=b.product_id and concat(a.year,a.month)=b.id_month
where a.year is not null
)
select product_id,id_month,total_revenue,'CREATED' as tipe
from
(
select created.product_id,created.id_month,real.total_revenue,
rank () over (partition by created.product_id,created.id_month order by (created.r-real.r) asc) as r
from
created left join real on created.product_id=real.product_id
and created.id_month > real.id_month
)a
where r=1
union
select product_id,concat(year,month) as id_month,total_revenue,'REAL' as tipe
from revenue

SQL: Difference between consecutive rows

You can use lag() to get the date of the previous order by the same customer:

select o.*,
    datediff(
        order_date,
        lag(order_date) over(partition by member_id order by order_date, order_id)
    ) days_diff
from orders o

When there are two rows for the same date, the smallest order_id is considered first. Also note that I fixed your datediff() syntax: in Hive, the function just takes two dates, and no unit.

I just don't get the logic you want to compute num_orders.

SQL: How to compare consecutive rows in SQL Server

Use GROUP BY and EXISTS

select client, 
       count(*) [sms count],
       (
         select count(*) 
         from data t
         where t.client = d.client and
               ttype = 'sms' and exists(
                 select 1
                 from data h
                 where h.client = t.client and
                       h.day between t.day and t.day + 4 and
                       h.outcome = 'pos'
               )
       ) [eff sms count]
from data d
where ttype = 'sms'
group by client

DBFiddle DEMO

EDIT: Alternative solution

select d1.client, 
       d1.[sms count],
       d2.[eff sms count]
from (
    select client, 
       count(*) [sms count]
    from data
    where ttype = 'sms'
    group by client
) d1
join 
(
    select client, count(*) [eff sms count]
    from data t
    where  ttype = 'sms' and exists(
        select 1
        from data h
        where h.client = t.client and
           h.day between t.day and t.day + 4 and
           h.outcome = 'pos'
    )
    group by t.client
) d2 on d1.client = d2.client

DBFiddle DEMO

For both solutions the followinf indexes should be present

data(ttype, client)
data(outcome, client, day)

---

Related Topics