9

I've got a table of orders

   Column   |            Type             |                      Modifiers                      
------------+-----------------------------+-----------------------------------------------------
 id         | integer                     | not null default nextval('orders_id_seq'::regclass)
 client_id  | integer                     | not null
 start_date | date                        | not null
 end_date   | date                        | 
 order_type | character varying           | not null

The data has non overlapping standing orders for a client_id and occasionally a temporary order that overrides the standing order on it's start_date when they have a matching client_id. There is application level constraints keeping orders of the same type from overlapping.

 id | client_id | start_date |  end_date  | order_type 
----+-----------+------------+------------+------------
 17 |        11 | 2014-02-05 |            | standing
 18 |        15 | 2014-07-16 | 2015-07-19 | standing
 19 |        16 | 2015-04-01 |            | standing
 20 |        16 | 2015-07-18 | 2015-07-18 | temporary

For example, on 2015-07-18 client 16 has order #20 as it's active order because it overrides the standing order #19. With some fuss I found an efficient way of querying for active order id's on a date.

    SELECT id from (
      SELECT
        id,
        first_value(id) OVER (PARTITION BY client_id ORDER BY order_type DESC) active_order_id
      FROM orders
      WHERE start_date <= ? and (end_date is null OR end_date >= ?)
    ) active_orders
    WHERE id = active_order_id

If you query this with 2015-07-18 as the placeholders, you would get

 id 
----
 17
 18
 20

The query plan on this query compared to some of my other ideas (like sub queries counting the number of temporary orders for a client on a date) is quite small and I'm pretty happy with it. (the design of the table, I'm not thrilled about)

Now, I need a to find all the active orders for a date range joined with the dates they are active on. For example, with the date range of 2015-07-18 to 2015-07-19 I would like the following result.

active_date | id 
------------+----
 2015-07-18 | 17
 2015-07-18 | 18
 2015-07-18 | 20
 2015-07-19 | 17
 2015-07-19 | 18
 2015-07-19 | 19

Order 20 overrides order 19 on 2015-07-18 but not on 2015-07-19.

I found with generate_series() I can generate a range of dates, but I haven't a clue how to join that with this to get a table of dates and order id's. My hunch is a cross join but I can't figure out how to make that work in this circumstance.

Thanks

UPDATE Added an sql fiddle.

  • 2
    Could you show some example data? This active/non-active and temporary things are not very clear after the first read. – dezso Jul 31 '15 at 5:10
  • Yes, it's not clear. Your query will find one order per client and it doesn't seem to be deterministic. If there are 2 or more orders for a client, with same type, which of the two will be returned will be arbitrary and vary per execution. So, you either have some constraints on the table that you haven't told us or your query is not correct. – ypercubeᵀᴹ Jul 31 '15 at 10:50
  • I updated my question with a lot more details, and yes there are constraints on the data. – reconbot Jul 31 '15 at 18:03
5

I would use select distinct on instead of window function, then just join the days.

select 
    distinct on (date, client_id) date, 
    id 
from orders
inner join generate_series('2015-07-18'::date, '2015-07-19'::date, '1 day') date
  on start_date <= date and (end_date is null or date <= end_date)
order by date, client_id, order_type desc

http://sqlfiddle.com/#!15/5a420/16/0

I can elaborate more if something is not clear.

  • This doesn't cover the temporary order / standing order but that could be done after the join =) – reconbot Jul 31 '15 at 21:21
  • This specifies the same order as in your window query. So for any (date, client_id) it would select the first order_type in reversed alphabetic order. – Simon Perepelitsa Jul 31 '15 at 21:25
  • The inner join is perfect, and the select distinct is a lot easier to understand (and performs about just as well) than the window. Any other reason I shouldn't use the windowing functions? – reconbot Jul 31 '15 at 23:07
  • 1
    That's about it. I think distinct on is even more optimised than the window query. By the way, I should mention that this is a common "top-in-group" problem in SQL: stackoverflow.com/questions/3800551/… – Simon Perepelitsa Aug 1 '15 at 11:37
  • That's a great read, I have some studying to do. If you have some time I have an expanded version of this question that uses what I learned here. dba.stackexchange.com/questions/108767/… I'm sure I'll be back to update it with what I learn from that link. And Thanks – reconbot Aug 1 '15 at 18:00
0

Write a function that takes a single date as a parameter and returns a list of date + id's that have an order.

Then, use the generate_series as you suggested and call the function over the date range.

This is a common strategy when dealing with complex conditions in SQL.

I've included some code below, but the SQL answer above is much simpler.

Here's the function:

create or replace function o( date) returns setof INT AS '
SELECT id from (
 SELECT
  id,
  first_value(id) OVER (PARTITION BY client_id ORDER BY order_type DESC) active_order_id
 FROM orders
 WHERE start_date <= $1 and (end_date is null OR end_date >= $1)
) active_orders
WHERE id = active_order_id;
' LANGUAGE sql ;

And how to call it:

select distinct d, o(d::date) 
from generate_series('2015-07-18'::date, '2015-07-19'::date, '1 day') as d;

SQLFiddle

  • 2
    You may want to flush that answer out with some details, sample code, etc. As it is, this answer may get deleted since it is pretty vague. – Max Vernon Jul 31 '15 at 21:09
  • Would you be able to update my fiddle with an example? sqlfiddle.com/#!15/5a420/3/0 – reconbot Jul 31 '15 at 21:15
  • I've updated my answer to include some code but the answer above is simpler. – Don Drake Aug 1 '15 at 21:13

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.