sql - How to associate the next value of a field in a table next to the field itself? - Stack Overflow

admin管理员组

文章数量:1394049

I have a calendar table where each date has a flag associated with it if it is a vacation. I would like to create a derived table in which each holiday date is associated with the immediately following work date. For the size of the calendar this query does the job but is onerous. Is there any way to simplify the code?

select a.data_oss hd, min(b.data_oss) next_wd
from mts_calendar a 
join mts_calendar b on b.mts != 'H' and b.data_oss > a.data_oss and b.data_oss < a.data_oss +10
where a.mts = 'H'
group by a.data_oss

I have a calendar table where each date has a flag associated with it if it is a vacation. I would like to create a derived table in which each holiday date is associated with the immediately following work date. For the size of the calendar this query does the job but is onerous. Is there any way to simplify the code?

select a.data_oss hd, min(b.data_oss) next_wd
from mts_calendar a 
join mts_calendar b on b.mts != 'H' and b.data_oss > a.data_oss and b.data_oss < a.data_oss +10
where a.mts = 'H'
group by a.data_oss

• sql
• oracle-database

Share Improve this question Follow edited Mar 27 at 18:33 jarlh 44.8k88 gold badges5050 silver badges6767 bronze badges asked Mar 27 at 12:31 user30080503user30080503 922 bronze badges 10

• Your example seems to use the postgress SQL dialect. I created a fiddle that might help us collaborate on a optimal solution dbfiddle.uk/iUPdnBIw – Bart McEndree Commented Mar 27 at 12:44
• How do you want to handle multiple holidays in a row? For example xmas eve and xmas. – Bart McEndree Commented Mar 27 at 12:45
• What is the reason for the join criteria b.data_oss < a.data_oss +10 ? The example I created does not seem to need it. dbfiddle.uk/QmaMjgcu – Bart McEndree Commented Mar 27 at 12:50
• True, is not necessary. Assuming that it never happens that the first working day following a holiday falls more than 10 days ahead, I hope the condition could help reduce memory or computation usage. – user30080503 Commented Mar 27 at 13:27
• 1 When it comes to efficiency, don't trust a dbms general advice to be the best. A dbms specific solution can be a better option. – jarlh Commented Mar 27 at 18:37

 |  Show 5 more comments

2 Answers 2

Sorted by: Reset to default 0

The most efficient way to do that is not to use joins or subqueries at all, but window functions in a set of nested query blocks. There are actually a number of different ways this can be done. Here's one such way:

1. Start by querying all the data - not just the holidays.

2. Then use DECODE to NULL out the holidays in a derived column.

3. In a parent block, the FIRST_VALUE function with IGNORE NULLS and the proper windowing specification can easily get the next non-holiday date using that derived column from the inner block.

4. Once you've done that, it's simply a matter of filtering down to just holidays in the top-level block.

SELECT a.data_oss holiday_date,
       a.first_non_holiday_date
  FROM (SELECT a.*,
               FIRST_VALUE(non_holiday_date IGNORE NULLS) OVER (ORDER BY data_oss ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) first_non_holiday_date
          FROM (SELECT a.*,
                       DECODE(mts,'H',NULL,data_oss) non_holiday_date
                  FROM mts_calendar a) a) a
 WHERE mts = 'H'
 ORDER BY 1

As for optimization, it requires consideration of the structure of tables, indexes, amount of rows, etc.
If you present them, it will be possible to consider them in more detail.
In the meantime, for your consideration, query options without (explicit) JOIN.

See examples:
Next W-day for every date

select a.data_oss hd
  ,case when a.mts='W' then a.data_oss
   else (select min(b.data_oss)from mts_calendar b where b.data_oss>a.data_oss) 
   end next_wd
from mts_calendar a 
--  where a.mts = 'H'
order by a.data_oss

OR Next W-day for every H-day

select a.data_oss hd
  ,(select b.data_oss from mts_calendar b where b.data_oss>a.data_oss and b.mts='W'
    order by b.data_oss FETCH NEXT 1 ROWS ONLY)next_wd
from mts_calendar a 
where a.mts = 'H'
order by a.data_oss

fiddle

本文标签： sqlHow to associate the next value of a field in a table next to the field itselfStack Overflow