How to compare the current row with next and previous row in PostgreSQL?
This is my solution using WINDOW functions
. I used the lag
and lead
functions. Both returns a value from a column from a row in offset from the current row. lag
goes back and lead
goes next in the offset.
SELECT tokcat.text
FROM (
SELECT text, category, chartype, lag(category,1) OVER w as previousCategory, lead(category,1) OVER w as nextCategory
FROM token t, textBlockHasToken tb
WHERE tb.tokenId = t.id
WINDOW w AS (
PARTITION BY textBlockId, sentence
ORDER BY textBlockId, sentence, position
)
) tokcat
WHERE 'NAME' = ANY(previousCategory)
AND 'NAME' = ANY(nextCategory)
AND 'NAME' <> ANY(category)
Simplified version:
SELECT text
FROM (
SELECT text
,category
,lag(category) OVER w as previous_cat
,lead(category) OVER w as next_cat
FROM token t
JOIN textblockhastoken tb ON tb.tokenid = t.id
WINDOW w AS (PARTITION BY textblockid, sentence ORDER BY position)
) tokcat
WHERE category <> 'NAME'
AND previous_cat = 'NAME'
AND next_cat = 'NAME';
Major points
= ANY()
is not needed, the window function returns a single value- some redundant fields in the subquery
- no need to order by columns, that you
PARTITION BY
- the ORDER BY applies within partitions - Don't use mixed case identifiers without quoting, it only leads to confusion. (Better yet: don't use mixed case identifiers in PostgreSQL ever)
Compare a row with ALL the previous rows in SQL
There may be a different solution using analytic functions instead of a scalar sub query, however, I haven't quite figured it out yet. Meanwhile here's this solution:
with t1 as (
select yd.*
, ROW_NUMBER() OVER (PARTITION BY trip ORDER BY GamingDate, DateRedeemed) rn
from YourData yd
)
select t1.*
, (select sum(amount) from t1 t2
where t2.trip = t1.trip
and t2.rn <= t1.rn
and t2.gamingdate <= t1.gamingdate
and t1.gamingdate < t2.dateredeemed) Running
from t1
order by trip, rn;
DateRedeemed | GamingDate | Trip | Amount | rn | Running
:------------------ | :------------------ | ---: | -----: | :- | ------:
13/07/2017 00:00:00 | 03/07/2017 00:00:00 | 8001 | 100 | 1 | 100
17/07/2017 00:00:00 | 03/07/2017 00:00:00 | 8001 | 150 | 2 | 250
18/07/2017 00:00:00 | 04/07/2017 00:00:00 | 8001 | 125 | 3 | 375
27/07/2017 00:00:00 | 16/07/2017 00:00:00 | 8001 | 250 | 4 | 525
28/07/2017 00:00:00 | 16/07/2017 00:00:00 | 8001 | 310 | 5 | 835
27/07/2017 00:00:00 | 17/07/2017 00:00:00 | 8001 | 125 | 6 | 810
31/07/2017 00:00:00 | 18/07/2017 00:00:00 | 8001 | 75 | 7 | 760
28/07/2017 00:00:00 | 27/07/2017 00:00:00 | 8001 | 80 | 8 | 465
31/07/2017 00:00:00 | 28/07/2017 00:00:00 | 8001 | 100 | 9 | 175
I do realize that the last two rows do not match the sample result, however, I think that's due to an error in the sample result rather than an error in my code. If you can explain why the sample result is correct for those two records I can try to rework my code.
[EDIT]
Here's an alternate version that avoids the inefficient scalar subquery in favor of using an analytic function:
With Actions as (
select *, GamingDate ActionDate, 1 DBCR from Table1
union all
select *, DateRedeemed ActionDate, -1 DBCR from Table1
), Analytics as (
select *
, sum(Amount*DBCR) over (partition by trip
order by ActionDate, dbcr
, GamingDate, DateRedeemed) Running
from Actions
)
select DateRedeemed, GamingDate, Trip, Amount, Running
from Analytics
where dbcr = 1
Compare Current Row with Previous/Next row in SQL Server
You can use the following query:
SELECT RaidNo, OutComeID, RN,
CASE
WHEN OutComeID <> 16 THEN 0
ELSE ROW_NUMBER() OVER (PARTITION BY OutComeID, grp ORDER BY RN)
END AS Result
FROM (
SELECT RaidNo, OutComeID, RN,
RN - ROW_NUMBER() OVER (PARTITION BY OutComeID ORDER BY RN) AS grp
FROM mytable) AS t
ORDER BY RN
Field grp
identifies slices (also called islands) of consecutive records having the same OutComeID
value. The outer query uses grp
in order to enumerate each record that belongs to a '16'
slice. The records that belong to the other slices are assigned value 0
.
Demo here
SQL. Comparing value from current row versus the value resulted from the same comparison of the previous row
It seems that you are looking for something like this:
DECLARE @DataSource TABLE
(
[RowID] INT
,[given] INT
);
INSERT INTO @DataSource ([RowID], [given])
VALUES (1, 10)
,(2, 15)
,(3, 12)
,(4, 14)
,(5, 20);
SELECT [given]
,MAX([given]) OVER (ORDER BY [RowID])
FROM @DataSource;
You can check the OVER clause as you may need to use PARTITION BY
or ROWS BETWEEN
in your real case.
SQL Find difference between previous and current row
See sqlFiddle
;WITH tblDifference AS
(
SELECT ROW_NUMBER() OVER(ORDER BY id) AS RowNumber, columnOfNumbers
FROM tableName
)
SELECT cur.columnOfNumbers, cur.columnOfNumbers - previous.columnOfNumbers
FROM tblDifference cur
LEFT OUTER JOIN tblDifference previous
ON cur.RowNumber = previous.RowNumber + 1
Related Topics
Update X Set Y = Null Takes a Long Time
Sql Server Reverse Order After Using Desc
Whats The Best Sqlite Data Type for a Long String
How to Convert Cyrillic Stored as Latin1 ( SQL ) to True Utf8 Cyrillic with Iconv
Hiberate Problems, Jdbc Identity_Insert Is Set to Off
Find Records from Previous X Days
How to Display All The Tables with More Information (Create Date, Size,...) in a MySQL Database
T-Sql Row Number Restart After N
How to Export Data from SQL Server 2008.2010 in Dml (Sql Script)
Unique Date Range Fields in SQL Server 2008
How to Join Two Unrelated Tables in Sql
Error While Uploading a Report
Postgresql Error: 42P01: Relation "[Table]" Does Not Exist
Performance Difference Between Primary Key and Unique Clustered Index in SQL Server
All Operator Vs Any on an Empty Query
Oracle SQL Return True If Exists Question
How to Insert into a Table with Just One Identity Column (Sql Express)