0

I am newbie to postgresql and still getting my head around. I have used windows function etc. till date and yes I can make some sense.

I have got the following data set:

enter image description here

I need to find those customers whose amount > 2 for 4 minutes in a rolling fashion, considering the data for a day. By that I mean in the given data set, customer 1 is one such as it has got amount > 2 at 16:01,16:02,16:03,16:04 and then at 16:02,16:03,16:04, 16:05. If there is any such single occurrence, the customer needs to be selected. Customer 2 doesn't have so. Wondering what's the best approach to do this?

Any help will be much appreciated.

Regards,

  • 1
    It would be better if you provided the data as text, not screenshot. And if you add the CREATE TABLE statement. – ypercubeᵀᴹ Dec 17 '18 at 9:49
  • customers whose amount > 2 for 4 minutes in a rolling fashion This means that ALL records within a priod have amount > 2? And - you say "for 4 minutes", but such period includes 5 values, not 4... – Akina Dec 17 '18 at 9:49
  • Also, regarding the details. Are all data in the table spaced by 1 minute? Or can it happen that you have rows with 16:00 and 16:01, then more with 16:03 and 16:04 and what should happen in that case? Does the all > 2 count or not, when the 16:02 is missing? – ypercubeᵀᴹ Dec 17 '18 at 9:51
  • hi @Akina, yes ALL records within a period have amount > 2. Not sure what you mean by 'but such period includes 5 values, not 4'. Do you mean you start at 16:00 and you scan till 16:04, you start at 16:01 and you scan till 16:05. I think yes makes sense – app_developer Dec 18 '18 at 4:20
  • @ypercubeᵀᴹ, here is the create statement: CREATE TABLE sales ( customer BIGINT, purshase_ts TIMESTAMP WITH TIME ZONE, amount NUMERIC ); – app_developer Dec 18 '18 at 4:43
0

Schematically:

SELECT DISTINCT t1.customer
FROM test t1, test t2
WHERE t2.purshase_ts BETWEEN t1.purshase_ts AND t1.purshase_ts + INTERVAL 4 minute
  AND t1.customer = t2.customer
GROUP BY t1.customer, t1.purshase_ts
HAVING SUM(CASE WHEN t2.amount > 2 THEN 1 ELSE 0 END) = COUNT(t2.amount)

or

SELECT DISTINCT t1.customer
FROM test t1
WHERE NOT EXISTS (SELECT 1
                  FROM test t2
                  WHERE t2.purshase_ts BETWEEN t1.purshase_ts AND t1.purshase_ts + INTERVAL 4 minute
                    AND t1.customer = t2.customer
                    AND t2.amount <= 2)

If time roll is to be "full", use 1st query adding relevant restriction:

SELECT DISTINCT t1.customer
FROM test t1, test t2
WHERE t2.purshase_ts BETWEEN t1.purshase_ts AND t1.purshase_ts + INTERVAL '4 minute'
  AND t1.customer = t2.customer
GROUP BY t1.customer, t1.purshase_ts
HAVING SUM(CASE WHEN t2.amount > 2 THEN 1 ELSE 0 END) = COUNT(t2.amount)
   AND COUNT(t2.amount) = 5

or

SELECT DISTINCT t1.customer
FROM test t1, test t2
WHERE t2.purshase_ts BETWEEN t1.purshase_ts AND t1.purshase_ts + INTERVAL '4 minute'
  AND t1.customer = t2.customer
GROUP BY t1.customer, t1.purshase_ts
HAVING SUM(CASE WHEN t2.amount > 2 THEN 1 ELSE 0 END) = COUNT(t2.amount)
   AND MAX(t2.purshase_ts) - MIN(t2.purshase_ts) = INTERVAL '4 MINUTE'

fiddle

  • Suppose I have the following data: customer,purshase_ts,amount 1,2018-12-18 16:00:00.000000 +11:00,2 1,2018-12-18 16:01:00.000000 +11:00,2 1,2018-12-18 16:02:00.000000 +11:00,1 1,2018-12-18 16:03:00.000000 +11:00,3 1,2018-12-18 16:04:00.000000 +11:00,5 1,2018-12-18 16:05:00.000000 +11:00,5 2,2018-12-18 16:00:00.000000 +11:00,2 2,2018-12-18 16:05:00.000000 +11:00,2 2,2018-12-18 16:10:00.000000 +11:00,4 If I run the either of the above queries, I get both 1 and 2. That is not correct as none of the customers has got amount > 2 for rolling 4 minutes – app_developer Dec 18 '18 at 4:50
  • @app_developer That IS correct. For example, customer 1 have amount > 2 for rolling 4 minutes within period from 16:03 till 16:07. Of course, the data for 16:06 and 16:07 is absent in a table, but I do NOT see any restrictions of absent points. – Akina Dec 18 '18 at 6:50
  • @app_developer "Full pack" restrictions added - check. – Akina Dec 18 '18 at 7:01
  • I reckon the updated query only works if the purshahe_ts interval is 1 min. With the query below: SELECT DISTINCT t1.customer FROM test t1, test t2 WHERE t2.purshase_ts BETWEEN t1.purshase_ts AND t1.purshase_ts + INTERVAL '4 minute' AND t1.customer = t2.customer GROUP BY t1.customer, t1.purshase_ts HAVING SUM(CASE WHEN t2.amount > 1 THEN 1 ELSE 0 END) = COUNT(t2.amount) AND MAX(t2.purshase_ts) - MIN(t2.purshase_ts) = INTERVAL '4 MINUTE' I expect to see both 1 and 2. Because for 2, just considering the first 2 records, amount is > 1 for 5 mins which is greater than 4 mins – app_developer Dec 18 '18 at 11:06
  • @app_developer Do not post code in comments - it's unreadable... Edit fiddle and post link to it. – Akina Dec 18 '18 at 11:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.