1

currently I have the following table:

CREATE TABLE demo (
  id SERIAL PRIMARY KEY,
  key TEXT NOT NULL,
  other_key TEXT NOT NULL,
  quantity BIGINT NOT NULL,
  date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

Now I want to group by the query like that:

SELECT other_key, SUM(quantity) FROM demo GROUP BY other_key;       

This works so far so good, however now I want to filter by the key and also print the latest date for the table, is there a good way to do so?

Pseudo (will fail since key is not in group by)

SELECT other_key, SUM(quantity), MAX(date) FROM demo GROUP BY other_key WHERE key = ?;    

my initial idea is a sub query:

SELECT other_key, SUM(quantity), MAX(date) FROM (SELECT * FROM demo WHERE key = ?) GROUP BY other_key;

Is there a better way to do so? And what would be a good index for the table?

My current index is:

CREATE INDEX demo_all_idx ON demo (key, other_key, quantity);

Bonus (edited):

  • Is there any way to sort by the MAX(date) then?
  • Is there a way to create a aggregate function that gets the oldest date where the quantity is great than zero? i.e. some kind of inventory event store where the newest date should not be the latest entry made and instead the newest entry made where the quantity isn't subtracted / zero? like consider the following table:

    id | key     | other_key | quantity | date
    ---+---------+-----------+----------+-----------------------
     6 | 0A19882 | 01/01     |      100 | 2016-08-30 00:00:00+02
     7 | 0A19882 | 01/02     |      -50 | 2016-09-01 00:00:00+02
     8 | 0A19882 | 01/01     |      100 | 2016-09-02 00:00:00+02
     9 | 0A19882 | 01/02     |      100 | 2016-08-31 00:00:00+02
    11 | 0A19882 | 01/03     |      100 | 2016-08-31 00:00:00+02
    12 | 0A19882 | 01/03     |     -100 | 2016-09-02 00:00:00+02
    13 | 0A19882 | 01/03     |      100 | 2016-09-04 00:00:00+02
    

The date for 01/01 should be 2016-08-30 00:00:00+02 while for 01/03 it should be 2016-09-04 00:00:00+02 since the event with id 12 has reached zero.

2

The WHERE clause goes before the GROUP BY:

SELECT 
    other_key, 
    SUM(quantity) AS sum_quantity,
    MAX(date)     AS max_date 
FROM demo 
WHERE key = ?
GROUP BY other_key 
ORDER BY max_date ; 

By the way key is a reserved keyword in SQL - although it isn't in Postgres. It would be best to be avoided as a column or table name.

For the additional question, to also calculate the cumulative sums (order by date) and then find the (oldest) date that these sums went positive and stayed positive, it's easier done with some window functions:

SELECT 
    other_key,  
    total_sum_quantity, max_date,
    CASE WHEN cumulative_sum > 0 THEN cumulative_sum END AS cumulative_sum,
    CASE WHEN cumulative_sum > 0 THEN date END AS oldest_positive_strike_date
FROM
  ( SELECT 
        *,
        ROW_NUMBER()
            OVER (PARTITION BY other_key
                  ORDER BY date DESC)  AS rn   
    FROM 
      ( SELECT 
            other_key, quantity, date,
            SUM(quantity) OVER (PARTITION BY key, other_key) AS total_sum_quantity,
            MAX(date) OVER (PARTITION BY key, other_key)     AS max_date,
            SUM(quantity) OVER (PARTITION BY key, other_key
                                ORDER BY date)               AS cumulative_sum,
            LAG(quantity) OVER (PARTITION BY key, other_key
                                ORDER BY date)               AS prev_quantity
        FROM demo 
        WHERE key = '0A19882'
      ) AS t
    WHERE (cumulative_sum  > 0 AND cumulative_sum-quantity <= 0)
       OR (cumulative_sum <= 0 AND cumulative_sum-quantity  > 0)
       OR (prev_quantity IS NULL)
  ) AS t2
WHERE rn = 1 ;

Test at rextester.com.

Some notes:

  • The cumulative_sum returned is the cumulative sum at the point of the oldest_positive_strike_date. If the total cumulative sum is not positive, both these columns will show NULL.
  • The PARTITION BY key, other_key can be replaced by PARTITION BY other_key. I left as it is, in case you need to run the query not just with one key value but with more, eg. for the whole table or with WHERE key IN (...).
  • The ORDER BY date will be deterministic if the (key, other_key, date) has a UNIQUE constraint/index. If there is a chance that you have two rows with same key, other_key and date, replace that with something that can identify a row, eg. ORDER BY date, id.
  • The "obvious" index that will benefit the query would be on (key, other_key, date, quantity). Postgres may choose a different plan though, scanning the table or using the index and also checking the values against the table. this depends on various factors. Experiment with various table sizes and with the work load you expect.
  • Since the initial WHERE key = ? condition will restrict the rows to about 100 (from a 100K table), it might be more efficient to use a CTE that gets these rows first, using something like the following. You may get away with a simple index on (key) and have good performance:

    WITH a AS
      ( SELECT * 
        FROM demo
        WHERE key = ?
      ) 
    SELECT ... ;          --- the query as it is, without the `WHERE`
    
  • well your answer is good, however it doesn't solve the bonus. well even the sub select gives back the same query than just putting the where clause first, like you did. so cte, sub select and where clause will be planned the same. – Christian Schmitt Oct 6 '16 at 12:02
  • 1
    Let us continue this discussion in chat. – ypercubeᵀᴹ Oct 6 '16 at 13:14

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.