sql - Multiple correlated subqueries with different conditions to same table -


i have 2 tables:

orders

| id | item_id | quantity | ordered_on | |----|---------|----------|------------| |  1 |    1    |    2     | 2016-03-09 | |  2 |    1    |    2     | 2016-03-12 | |  3 |    4    |    3     | 2016-03-15 | |  4 |    4    |    3     | 2016-03-13 | 

stocks

| id | item_id | quantity | enter_on   | expire_on  | |----|---------|----------|------------|------------| |  1 |    1    |   10     | 2016-03-07 | 2016-03-10 | |  2 |    1    |   20     | 2016-03-11 | 2016-03-15 | |  3 |    1    |   20     | 2016-03-14 | 2016-03-17 | |  4 |    4    |   10     | 2016-03-14 |    null    | |  5 |    4    |   10     | 2016-03-12 |    null    | 

i'm trying create view show orders along closest stocks enter_on (i'm using include_after , include_before give overview on date want exclude item that's preordered, stock reflect correctly.)

include_after going stock came in not expired yet, if expired, show null, include_before show next incoming stock enter_on, unless there's expire_on that's earlier next enter_on.

| item_id | quantity | ordered_on | include_after | include_before | |---------|----------|------------|---------------|----------------| |    1    |    2     | 2016-03-09 |  2016-03-07   |   2016-03-10   | |    1    |    2     | 2016-03-12 |  2016-03-11   |   2016-03-14   | |    4    |    3     | 2016-03-13 |  2016-03-12   |   2016-03-14   | |    4    |    3     | 2016-03-15 |  2016-03-14   |      null      | 

so came with:

select   o.item_id, o.quantity, o.order_on, (     select coalesce(max(s.enter_on), null::date)     stocks s     s.enter_on <= o.order_on , s.item_id = o.item_id   ) include_after, (     select coalesce(min(s.enter_on), null::date)     stocks s     s.enter_on > o.order_on , s.item_id = o.item_id   ) include_before   orders o; 

it works fine (i haven't included expire_on part), i'm worrying performance issue using 2 subqueries in select.

does have alternative suggestions?

update

i'm using postgresql 9.4 (can't add anymore tags)

the actual problem way more complicated stated, it's lot of tables joined , views, shrunk down 1 table grasp concept if there alternatives

you should worry performance when situation arises. example provided, index on stocks(item_id, enter_on, expire_on) should sufficient. might want 2 indexes: stocks(item_id, enter_on desc, expire_on).

if performance not sufficient, have 2 choices. 1 gist index ranges. (here interesting discussion of issue.) second alternative query formulation.

however, attempt optimize query until there enough data show performance problem. solutions on smaller amounts of data might not scale well.


Comments

Popular posts from this blog

wordpress - (T_ENDFOREACH) php error -

Export Excel workseet into txt file using vba - (text and numbers with formulas) -

Using django-mptt to get only the categories that have items -