Postgres window function using group and lag and other possible ways












0















Assume I have the table below and I would like to return only those rows where Price != price in the previous row but only within a group where the grouping is by Time



ID :           {    1   ,  2      ,      3   ,    4    ,    5    ,    6 }

Time : { 09:20:00, 09:20:00, 09:20:00, 09:20:01, 09:20:02, 09:20:03 }

Price : { 100 , 100 , 101 , 102 , 102 , 103 }


Because of the grouping by time, the output returned should be:



ID                   : {   1     ,    3    ,    4    ,   5      ,  6 }
Time : { 09:20:00, 09:20:00, 09:20:01, 09:20:02 , 09:20:03 }
Price : { 100 , 101 , 102 , 102 , 103 }


Do notice that the output discards one of the 100 price at 09:20 but does not discard 102 price at 09:20:02 even though it is same as price at 09:20:01 since the grouping has to be done by time. Can someone help me with a postgres query for something like this.










share|improve this question

























  • How is the order within a group defined?

    – Laurenz Albe
    Nov 15 '18 at 18:50













  • There is a unique id for every row, basically 3 columns ID, Time and Price. I have edited the table structure above.

    – aajkaltak
    Nov 15 '18 at 18:54


















0















Assume I have the table below and I would like to return only those rows where Price != price in the previous row but only within a group where the grouping is by Time



ID :           {    1   ,  2      ,      3   ,    4    ,    5    ,    6 }

Time : { 09:20:00, 09:20:00, 09:20:00, 09:20:01, 09:20:02, 09:20:03 }

Price : { 100 , 100 , 101 , 102 , 102 , 103 }


Because of the grouping by time, the output returned should be:



ID                   : {   1     ,    3    ,    4    ,   5      ,  6 }
Time : { 09:20:00, 09:20:00, 09:20:01, 09:20:02 , 09:20:03 }
Price : { 100 , 101 , 102 , 102 , 103 }


Do notice that the output discards one of the 100 price at 09:20 but does not discard 102 price at 09:20:02 even though it is same as price at 09:20:01 since the grouping has to be done by time. Can someone help me with a postgres query for something like this.










share|improve this question

























  • How is the order within a group defined?

    – Laurenz Albe
    Nov 15 '18 at 18:50













  • There is a unique id for every row, basically 3 columns ID, Time and Price. I have edited the table structure above.

    – aajkaltak
    Nov 15 '18 at 18:54
















0












0








0








Assume I have the table below and I would like to return only those rows where Price != price in the previous row but only within a group where the grouping is by Time



ID :           {    1   ,  2      ,      3   ,    4    ,    5    ,    6 }

Time : { 09:20:00, 09:20:00, 09:20:00, 09:20:01, 09:20:02, 09:20:03 }

Price : { 100 , 100 , 101 , 102 , 102 , 103 }


Because of the grouping by time, the output returned should be:



ID                   : {   1     ,    3    ,    4    ,   5      ,  6 }
Time : { 09:20:00, 09:20:00, 09:20:01, 09:20:02 , 09:20:03 }
Price : { 100 , 101 , 102 , 102 , 103 }


Do notice that the output discards one of the 100 price at 09:20 but does not discard 102 price at 09:20:02 even though it is same as price at 09:20:01 since the grouping has to be done by time. Can someone help me with a postgres query for something like this.










share|improve this question
















Assume I have the table below and I would like to return only those rows where Price != price in the previous row but only within a group where the grouping is by Time



ID :           {    1   ,  2      ,      3   ,    4    ,    5    ,    6 }

Time : { 09:20:00, 09:20:00, 09:20:00, 09:20:01, 09:20:02, 09:20:03 }

Price : { 100 , 100 , 101 , 102 , 102 , 103 }


Because of the grouping by time, the output returned should be:



ID                   : {   1     ,    3    ,    4    ,   5      ,  6 }
Time : { 09:20:00, 09:20:00, 09:20:01, 09:20:02 , 09:20:03 }
Price : { 100 , 101 , 102 , 102 , 103 }


Do notice that the output discards one of the 100 price at 09:20 but does not discard 102 price at 09:20:02 even though it is same as price at 09:20:01 since the grouping has to be done by time. Can someone help me with a postgres query for something like this.







postgresql group-by lag






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 15 '18 at 18:57







aajkaltak

















asked Nov 15 '18 at 18:43









aajkaltakaajkaltak

47231026




47231026













  • How is the order within a group defined?

    – Laurenz Albe
    Nov 15 '18 at 18:50













  • There is a unique id for every row, basically 3 columns ID, Time and Price. I have edited the table structure above.

    – aajkaltak
    Nov 15 '18 at 18:54





















  • How is the order within a group defined?

    – Laurenz Albe
    Nov 15 '18 at 18:50













  • There is a unique id for every row, basically 3 columns ID, Time and Price. I have edited the table structure above.

    – aajkaltak
    Nov 15 '18 at 18:54



















How is the order within a group defined?

– Laurenz Albe
Nov 15 '18 at 18:50







How is the order within a group defined?

– Laurenz Albe
Nov 15 '18 at 18:50















There is a unique id for every row, basically 3 columns ID, Time and Price. I have edited the table structure above.

– aajkaltak
Nov 15 '18 at 18:54







There is a unique id for every row, basically 3 columns ID, Time and Price. I have edited the table structure above.

– aajkaltak
Nov 15 '18 at 18:54














1 Answer
1






active

oldest

votes


















1














SELECT time, price
FROM mytable
WHERE price <> coalesce(
lag(price) OVER (PARTITION BY time ORDER BY id),
price - 1
)
ORDER BY time, id;


The coalesce takes care of the NULL value that appears for the first element in each partition.






share|improve this answer
























  • thank you! that worked

    – aajkaltak
    Nov 15 '18 at 19:21











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53326015%2fpostgres-window-function-using-group-and-lag-and-other-possible-ways%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









1














SELECT time, price
FROM mytable
WHERE price <> coalesce(
lag(price) OVER (PARTITION BY time ORDER BY id),
price - 1
)
ORDER BY time, id;


The coalesce takes care of the NULL value that appears for the first element in each partition.






share|improve this answer
























  • thank you! that worked

    – aajkaltak
    Nov 15 '18 at 19:21
















1














SELECT time, price
FROM mytable
WHERE price <> coalesce(
lag(price) OVER (PARTITION BY time ORDER BY id),
price - 1
)
ORDER BY time, id;


The coalesce takes care of the NULL value that appears for the first element in each partition.






share|improve this answer
























  • thank you! that worked

    – aajkaltak
    Nov 15 '18 at 19:21














1












1








1







SELECT time, price
FROM mytable
WHERE price <> coalesce(
lag(price) OVER (PARTITION BY time ORDER BY id),
price - 1
)
ORDER BY time, id;


The coalesce takes care of the NULL value that appears for the first element in each partition.






share|improve this answer













SELECT time, price
FROM mytable
WHERE price <> coalesce(
lag(price) OVER (PARTITION BY time ORDER BY id),
price - 1
)
ORDER BY time, id;


The coalesce takes care of the NULL value that appears for the first element in each partition.







share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 15 '18 at 19:00









Laurenz AlbeLaurenz Albe

50.3k102950




50.3k102950













  • thank you! that worked

    – aajkaltak
    Nov 15 '18 at 19:21



















  • thank you! that worked

    – aajkaltak
    Nov 15 '18 at 19:21

















thank you! that worked

– aajkaltak
Nov 15 '18 at 19:21





thank you! that worked

– aajkaltak
Nov 15 '18 at 19:21




















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53326015%2fpostgres-window-function-using-group-and-lag-and-other-possible-ways%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

Bressuire

Vorschmack

Quarantine