Last active
February 11, 2019 12:18
-
-
Save denegny/2b400ff89093dbfe6bb3c5c3cfd1f04b to your computer and use it in GitHub Desktop.
last price & percent diff
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- PostgreSQL dialect | |
| -- drop table if exist | |
| -- drop table ttest; | |
| --create new table | |
| create table ttest | |
| ( | |
| date_w date, | |
| nname varchar(50), | |
| price money | |
| ); | |
| --insert rows | |
| insert into ttest (date_w, nname, price) | |
| values ('01.10.2011', 'Номенклатура1', '5'); | |
| insert into ttest (date_w, nname, price) | |
| values ('02.10.2011', 'Номенклатура1', '1'); | |
| insert into ttest (date_w, nname, price) | |
| values ('12.10.2011', 'Номенклатура1', '2'); | |
| insert into ttest (date_w, nname, price) | |
| values ('04.10.2011', 'Номенклатура2', '1'); | |
| insert into ttest (date_w, nname, price) | |
| values ('07.10.2011', 'Номенклатура2', '1.20'); | |
| insert into ttest (date_w, nname, price) | |
| values ('13.10.2011', 'Номенклатура2', '5.20'); | |
| insert into ttest (date_w, nname, price) | |
| values ('01.01.2011', 'Номенклатура3', '70'); | |
| insert into ttest (date_w, nname, price) | |
| values ('05.05.2011', 'Номенклатура3', '99'); | |
| insert into ttest (date_w, nname, price) | |
| values ('01.07.2011', 'Номенклатура3', '70'); | |
| --list table row | |
| select * | |
| from ttest; | |
| --last price & percent diff | |
| select tt.*, | |
| t4.price as price_curr, | |
| t3.price as price_prev, | |
| cast(100 * (t4.price - t3.price) / t3.price as numeric(5, 2)) as price_percent | |
| from ( | |
| select t2.date_w, t2.nname, max(t1.date_w) as date_prev | |
| from ttest t1 | |
| left join (select nname, max(date_w) as date_w from ttest group by nname) as t2 | |
| on t1.nname = t2.nname and t1.date_w < t2.date_w | |
| group by t2.date_w, t2.nname) as tt | |
| join ttest t3 on t3.date_w = tt.date_prev and t3.nname = tt.nname | |
| join ttest t4 on t4.date_w = tt.date_w and t4.nname = tt.nname |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment