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)
Postgres Query Based on Previous and Next Rows
I am not sure if I understood your problem correctly. But getting values from other rows this can be done by window functions (https://www.postgresql.org/docs/current/static/tutorial-window.html):
demo: db<>fiddle
SELECT
id,
lag("to") OVER (ORDER BY id) as prev_to,
"from",
"to",
lead("from") OVER (ORDER BY id) as next_from
FROM bustimes;
The lag
function moves the value of the previous row into the current one. The lead
function does the same with the next row. So you are able to calculate a difference between last arrival and current departure or something like that.
Result:
id prev_to from to next_from
18 33000 33300 33300
19 33300 33300 33600 33900
20 33600 33900 34200 34200
21 34200 34200 34800 36000
22 34800 36000 36300
Please notice that "from" and "to" are reserved words in PostgreSQL. It would be better to chose other names.
Comparing record to previous record in postgresql
SELECT client,
rate,
startdate,
enddate,
lag(rate) over client_window as pre_rate,
lag(startdate) over client_window as pre_startdate,
lag(enddate) over client_window as pre_enddate
FROM the_table
WINDOW client_window as (partition by client order by startdate)
ORDER BY client, stardate;
This assumes that enddate is always greater than startdate from the same row and that no enddate is greater than the following startdate
Compare Current Row with Previous 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
In PostgreSQL, how to select the previous row value to compute the current row value?
This will do it. It's not especially efficient though, but for a one off it should do.
select x.id, sum(coalesce(y.value1,y.id,0))
from sample x
left outer join sample y on x.id >= y.id
group by x.id
order by x.id
Basically, it goes through every record in the table, and then sums up the IDs of all the records up to and including that record. There is a special logic to handle the initial value you've got on id=1.
SQL Fiddle at http://sqlfiddle.com/#!9/b8f25/1
Another option is to use a window function.
select x.id, sum(coalesce(x.value1,x.id,0)) over (order by x.id)
from sample x
SQL Fiddle at http://sqlfiddle.com/#!17/b8f25/2
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
Related Topics
Incomplete Information from Query on Pg_Views
Differencebetween SQL, Pl-SQL and T-Sql
How to Backup a Remote SQL Server Database to a Local Drive
How to Find a Table Having a Specific Column in Postgresql
Adding a New SQL Column with a Default Value
How to Cast a String to Integer and Have 0 in Case of Error in the Cast with Postgresql
Postgresql: Insert into ... (Select * ...)
Use '=' or Like to Compare Strings in SQL
Can Parameterized Statement Stop All SQL Injection
How to Do If Not Exists in SQLite
How to Show Row Numbers in Postgresql Query
How to Get N Rows Starting from Row M from Sorted Table in T-Sql
Boolean VS Tinyint(1) for Boolean Values in MySQL
Trim Trailing Spaces with Postgresql
Is of a Type That Is Invalid for Use as a Key Column in an Index
Differencebetween Group by and Order by in SQL
Creating Temporary Database That Works Across Maven Test Phases