Reputation: 185
id year
1 2017
1 2018
1 2019
2 2018
2 2019
3 2017
3 2019
8 2017
4 2018
4 2019
I need to create column based on id and year column:
output :
id year mark
1 2017 P
1 2018 P
1 2019 N
2 2018 P
2 2019 N
3 2017 N
3 2019 N
8 2017 P
4 2018 P
4 2019 N
Upvotes: 2
Views: 258
Reputation: 44250
You don't need a physical column. What you want can be expressed as a query, using exists()
or lead()
the LEAD()
version:
\i tmp.sql
CREATE TABLE years(id integer, zyear integer);
INSERT INTO years (id , zyear ) VALUES
(1, 2017) , (1, 2018) , (1, 2019)
, (2, 2018) , (2, 2019) , (3, 2017)
, (3, 2019) , (8, 2017)
, (4, 2018) , (4, 2019)
;
SELECT id, zyear
, CASE when yy.nxt=yy.zyear+1 THEN 'P' ELSE 'N' END AS flagged
FROM (
SELECT id, zyear
, lead(zyear) OVER (partition by id ORDER BY zyear) AS nxt
FROM years
) yy
;
or, the EXISTS()
-version:
SELECT id, zyear
, CASE when yy.xx THEN 'P' ELSE 'N' END AS flagged
FROM (
SELECT id, zyear
, EXISTS ( select * FROM years x where x.id=y.id and x.zyear = y.zyear+1) AS xx
FROM years y
) yy
;
Result: (the same for both versions)
psql:tmp.sql:2: NOTICE: drop cascades to table tmp.years
DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
INSERT 0 10
id | zyear | flagged
----+-------+---------
1 | 2017 | P
1 | 2018 | P
1 | 2019 | N
2 | 2018 | P
2 | 2019 | N
3 | 2017 | N
3 | 2019 | N
4 | 2018 | P
4 | 2019 | N
8 | 2017 | N
(10 rows)
Upvotes: 0
Reputation: 362
You can try Lead() function. but please check output for Id = 8. Ideally it should be 'N'
SELECT *
,CASE WHEN LEAD(Year) OVER (PARTITION BY ID ORDER BY YEAR) - YEAR = 1 THEN 'P' ELSE 'N' END
FROM #Table
Upvotes: 2
Reputation: 1270883
Hmmm . . . I'm thinking to generate flags for each year and then apply the logic. Based on the rules you describe:
select t.*,
(case when year in (2017, 2018) and
flag_2017 > 0 and flag_2018 > 0
then 'P'
when year in (2017) and
flag_2018 > 0 and flag_2019 > 0
then 'P'
else 'N'
end) as mark
from (select t.*,
sum(case when year = 2017 then 1 else 0 end) over (partition by id) as flag_2017,
sum(case when year = 2018 then 1 else 0 end) over (partition by id) as flag_2018,
sum(case when year = 2019 then 1 else 0 end) over (partition by id) as flag_2019
from t
) t;
Your sample results don't seem to follow your rules, but some simple variation on this appears to be what you want.
Upvotes: 0