1 2 Previous Next 21 Replies Latest reply: Apr 30, 2012 12:33 PM by Stew Ashton RSS

    query

    babaravi
      Hi,


      is it possible to achieve the below output in sql? below data are sample data. we have huge voulme of data( say like 40 millions of record.)
      please help me to do it in sql.


      Data
      ===
      SQL> select *
        2  from test_seats  g
        3   Order by dep_dt_sec1,flight_no_sec1,dep_dt_sec2 - g.arr_dt_sec1
        4  /
      
      DEP_DT_SEC1         FLIG ARR_DT_SEC1         DEP_DT_SEC2         FLIG AVAIL_SEATS_SEC1 AVAIL_SEATS_SEC2 
      ------------------- ---- ------------------- ------------------- ---- ---------------- ---------------- 
      24/04/2012 15:20:00 0788 24/04/2012 15:20:00 25/04/2012 09:50:00 0135               11                3 
      24/04/2012 15:20:00 0788 24/04/2012 15:20:00 25/04/2012 15:45:00 0137               11                7 
      24/04/2012 15:20:00 0788 24/04/2012 15:20:00 25/04/2012 20:45:00 0137               11                1 
      26/04/2012 02:20:00 0790 26/04/2012 06:20:00 27/04/2012 10:50:00 0135                5               10 
      26/04/2012 10:20:00 0791 26/04/2012 14:20:00 27/04/2012 10:50:00 0135                7               10 
      26/04/2012 18:20:00 0792 26/04/2012 22:20:00 27/04/2012 10:50:00 0135                2               10 
      output
      DEP_DT_SEC1         FLIG ARR_DT_SEC1         DEP_DT_SEC2         FLIG AVAIL_SEATS_SEC1 AVAIL_SEATS_SEC2    MIN_VAL  AVAIL_VAL
      ------------------- ---- ------------------- ------------------- ---- ---------------- ---------------- ---------- ----------
      24/04/2012 15:20:00 0788 24/04/2012 15:20:00 25/04/2012 09:50:00 0135               11                3          3          8
      24/04/2012 15:20:00 0788 24/04/2012 15:20:00 25/04/2012 15:45:00 0137               11                7          7          1
      24/04/2012 15:20:00 0788 24/04/2012 15:20:00 25/04/2012 20:45:00 0137               11                1          1          0
      26/04/2012 02:20:00 0790 26/04/2012 06:20:00 27/04/2012 10:50:00 0135                5               10          5          5
      26/04/2012 10:20:00 0791 26/04/2012 14:20:00 27/04/2012 10:50:00 0135                7               10          5          0
      26/04/2012 18:20:00 0792 26/04/2012 22:20:00 27/04/2012 10:50:00 0135                2               10          0          0
      Logic behind in the output is to get min value and available value.
      Min value logic
      Least value from these columns(AVAIL_SEATS_SEC1,AVAIL_SEATS_SEC2)

      Available value logic.
      Subtract the min value with Avail values.( AVAIL_SEATS_SEC1,AVAIL_SEATS_SEC2), for one to many comparison
      AVAIL_SEATS_SEC1 column is used to substract, for many to one comparison
      AVAIL_SEATS_SEC2 is used to substract

      Data is sorted by dep_dt_sec1,flight_no_sec1,dep_dt_sec2 - g.arr_dt_sec1

      Edited by: babaravi on Apr 25, 2012 2:52 PM
        • 1. Re: query
          Paul  Horth
          Isn't this a duplicate of Re: find the min value

          Anyway, I don't follow your logic.

          You have
          Logic behind in the output is to get min value and available value.
          Min value logic
          Least value from these columns(AVAIL_SEATS_SEC1,AVAIL_SEATS_SEC2)
          But I don't see why you have min_val of 5 on the 5th row as the least of 7 and 10 is 7.

          Also, why has the departure date of flight 0137 changed from 15:45 and 20:45 to 09:50?

          As to the available value logic, you need to explain that a lot clearer, with actual examples, equations and numbers involved.

          Please also provide create table scripts and insert test data scripts.

          Edited by: Paul Horth on 25-Apr-2012 01:41
          • 2. Re: query
            Paulie
            >

            is it possible to achieve the below output in sql? below data are sample data. we have huge voulme of data( say like 40 millions of record.)
            please help me to do it in sql.
            Data
            Can you please give us DDL and DML for the table - i.e. a create table statement a few
            sample data inserts (preferably tested by you). I used to work in an airline, so I might
            be able to help, but I'm not prepared to do your work for you - help us to help you! ;)

            DEP_DT_SEC2         FLIG AVAIL_SEATS_SEC1 AVAIL_SEATS_SEC2    MIN_VAL  AVAIL_VAL
            ------------------- ---- ---------------- ---------------- ---------- ----------
            25/04/2012 09:50:00 0135               11                3          3          8
            25/04/2012 09:50:00 0137               11                7          7          1  <---- if this isn't 4, I don't understand your logic.
            Logic behind in the output is to get min value and available value.
            Min value logic
            Least value from these columns(AVAIL_SEATS_SEC1,AVAIL_SEATS_SEC2)
            Paul...
            • 3. Re: query
              babaravi
              Hi Paulie,

              Here i have attached the scripts.
              Create  table 
              test_seats (
              dep_dt_sec1 DATE,
              flight_no_sec1 VARCHAR2(4),
              ARR_dt_sec1 DATE,
              dep_dt_sec2 DATE,
              flight_no_sec2 VARCHAR2(4),
              avail_seats_sec1 NUMBER(6),
              avail_seats_sec2 NUMBER(6))
              SQL> select *
                2  from test_seats  g
                3   Order by dep_dt_sec1,flight_no_sec1,dep_dt_sec2 - g.arr_dt_sec1
                4  /
              
              DEP_DT_SEC1         FLIG ARR_DT_SEC1         DEP_DT_SEC2         FLIG AVAIL_SEATS_SEC1 AVAIL_SEATS_SEC2 
              ------------------- ---- ------------------- ------------------- ---- ---------------- -------------
              24/04/2012 15:20:00 0788 24/04/2012 15:20:00 25/04/2012 09:50:00 0135               11                3 
              24/04/2012 15:20:00 0788 24/04/2012 15:20:00 25/04/2012 15:45:00 0137               11                7 
              24/04/2012 15:20:00 0788 24/04/2012 15:20:00 25/04/2012 20:45:00 0137               11                1 
              26/04/2012 02:20:00 0790 26/04/2012 06:20:00 27/04/2012 10:50:00 0135                5               10 
              26/04/2012 10:20:00 0791 26/04/2012 14:20:00 27/04/2012 10:50:00 0135                7               10 
              26/04/2012 18:20:00 0792 26/04/2012 22:20:00 27/04/2012 10:50:00 0135                2               10 
              insert into test_seats1 (DEP_DT_SEC1, FLIGHT_NO_SEC1, ARR_DT_SEC1, DEP_DT_SEC2, FLIGHT_NO_SEC2, AVAIL_SEATS_SEC1, AVAIL_SEATS_SEC2)
              values (to_date('24-04-2012 15:20:00', 'dd-mm-yyyy hh24:mi:ss'), '0788', to_date('24-04-2012 15:20:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('25-04-2012 09:50:00', 'dd-mm-yyyy hh24:mi:ss'), '0135', 11, 3);
              
              insert into test_seats1 (DEP_DT_SEC1, FLIGHT_NO_SEC1, ARR_DT_SEC1, DEP_DT_SEC2, FLIGHT_NO_SEC2, AVAIL_SEATS_SEC1, AVAIL_SEATS_SEC2)
              values (to_date('24-04-2012 15:20:00', 'dd-mm-yyyy hh24:mi:ss'), '0788', to_date('24-04-2012 15:20:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('25-04-2012 15:45:00', 'dd-mm-yyyy hh24:mi:ss'), '0137', 11, 7);
              
              insert into test_seats1 (DEP_DT_SEC1, FLIGHT_NO_SEC1, ARR_DT_SEC1, DEP_DT_SEC2, FLIGHT_NO_SEC2, AVAIL_SEATS_SEC1, AVAIL_SEATS_SEC2)
              values (to_date('24-04-2012 15:20:00', 'dd-mm-yyyy hh24:mi:ss'), '0788', to_date('24-04-2012 15:20:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('25-04-2012 20:45:00', 'dd-mm-yyyy hh24:mi:ss'), '0137', 11, 1);
              
              insert into test_seats1 (DEP_DT_SEC1, FLIGHT_NO_SEC1, ARR_DT_SEC1, DEP_DT_SEC2, FLIGHT_NO_SEC2, AVAIL_SEATS_SEC1, AVAIL_SEATS_SEC2)
              values (to_date('26-04-2012 02:20:00', 'dd-mm-yyyy hh24:mi:ss'), '0790', to_date('26-04-2012 06:20:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('27-04-2012 10:50:00', 'dd-mm-yyyy hh24:mi:ss'), '0135', 5, 10);
              
              insert into test_seats1 (DEP_DT_SEC1, FLIGHT_NO_SEC1, ARR_DT_SEC1, DEP_DT_SEC2, FLIGHT_NO_SEC2, AVAIL_SEATS_SEC1, AVAIL_SEATS_SEC2)
              values (to_date('26-04-2012 10:20:00', 'dd-mm-yyyy hh24:mi:ss'), '0791', to_date('26-04-2012 14:20:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('27-04-2012 10:50:00', 'dd-mm-yyyy hh24:mi:ss'), '0135', 7, 10);
              
              insert into test_seats1 (DEP_DT_SEC1, FLIGHT_NO_SEC1, ARR_DT_SEC1, DEP_DT_SEC2, FLIGHT_NO_SEC2, AVAIL_SEATS_SEC1, AVAIL_SEATS_SEC2)
              values (to_date('26-04-2012 18:20:00', 'dd-mm-yyyy hh24:mi:ss'), '0792', to_date('26-04-2012 22:20:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('27-04-2012 10:50:00', 'dd-mm-yyyy hh24:mi:ss'), '0135', 2, 10);
              Edited by: babaravi on Apr 25, 2012 3:08 PM

              Edited by: babaravi on Apr 25, 2012 3:21 PM
              • 4. Re: query
                babaravi
                Hi Paul Horth,

                But I don't see why you have min_val of 5 on the 5th row as the least of 7 and 10 is 7.
                See the first three rows, 11 is compared three values (3,7,1)

                In the first row.
                11 & 3 , 3 is the least value. available values is 11-3 = 8

                In the second row.

                Here we have to take the available value & compare (8 & 7) , 7 is the least value, available value is 8-7=1

                In the thrid row

                take available value and compare 1, 1 is the least value.
                Also, why has the departure date of flight 0137 changed from 15:45 and 20:45 to 09:50?
                The given data has been changed, now look at the data & understand.
                As to the available value logic, you need to explain that a lot clearer, with actual examples, equations and numbers involved.
                Explained it above.
                Please also provide create table scripts and insert test data scripts.
                Scripts have been provided
                • 5. Re: query
                  Stew Ashton
                  babaravi wrote:
                  Please also provide create table scripts and insert test data scripts.
                  Scripts have been provided
                  You did not provide "insert test data" scripts. We cannot insert the result of your select into our databases for testing purposes.

                  "Insert test data" scripts look like this:
                  INSERT INTO <table> (<list of columns>) VALUES(<list of values>);
                  • 6. Re: query
                    Paul  Horth
                    >
                    See the first three rows, 11 is compared three values (3,7,1)

                    In the first row.
                    11 & 3 , 3 is the least value. available values is 11-3 = 8

                    In the second row.

                    Here we have to take the available value & compare (8 & 7) , 7 is the least value, available value is 8-7=1

                    In the thrid row

                    take available value and compare 1, 1 is the least value.
                    >
                    What's that got to do with my question about min_val of 5? Where does 5 appear in your explanation?

                    >
                    Scripts have been provided
                    >

                    I see no INSERT scripts. I have not got time to take the output of a select and make it into INSERT statements. If you want busy people
                    to help you, you better put the effort in and write those INSERT scripts yourself.
                    • 7. Re: query
                      babaravi
                      What's that got to do with my question about min_val of 5? Where does 5 appear in your explanation?

                      ok. I explain you at 4th row.
                      5,10, 5 is the least. for avail value, 10-5=5
                      5,5, 5 is the least, for avail value,5-5=0
                      0,5 0 is the least, fif min val is 0, then avail value is consider as 0.

                       see no INSERT scripts. I have not got time to take the output of a select and make it into INSERT statements. If you want busy people
                      to help you, you better put the effort in and write those INSERT scripts yourself.
                      Sorry, forgot to give the insert script.
                      • 8. Re: query
                        Paulie
                        >

                        Here i have attached the scripts.
                        Great stuff. You should also post your version of Oracle - but for this it's not important.

                        I have your SQL - tested on 10 XE.
                        select avail_seats_sec1,  
                        avail_seats_sec2, 
                        Abs(avail_seats_sec1 - avail_seats_sec2) AS "Avail_val",
                        case when avail_seats_sec1 < avail_seats_sec2  
                        then avail_seats_sec1 
                        else avail_seats_sec2 
                        end as "Min_val"
                        from test_seats
                        If you have 11, you can use COMPUTED BY - then it's automatic. Still, I find
                        your table structure strange - what, exactly are sec1 and sec2?
                        Flight segments?

                        Sorry for COMPUTED BY read GENERATED AS (my Firebird/RDB heritage) see here
                        http://www.oracle-base.com/articles/11g/virtual-columns-11gr1.php

                        HTH, Paul...

                        Edited by: Paulie on 25-Apr-2012 11:12

                        Edited by: Paulie on 25-Apr-2012 11:32
                        • 9. Re: query
                          Paul  Horth
                          >
                          ok. I explain you at 4th row.
                          5,10, 5 is the least. for avail value, 10-5=5
                          5,5, 5 is the least, for avail value,5-5=0
                          0,5 0 is the least, fif min val is 0, then avail value is consider as 0.
                          >

                          Again, what has that got to do with a min_val of 5 on the 5th row?
                          • 10. Re: query
                            babaravi
                            Paulie,
                            your table structure strange - what, exactly are sec1 and sec2?
                            Flight segments?
                            It is sector. assume a person want to travel from BOMBAY To LONDON.

                            Sector1

                            Bombay to dubai.

                            Sector2

                            Dubai to London.

                            Basically. i am trying to find out the seat availability.
                            • 11. Re: query
                              Vivek L
                              may be (with lots of assumptions :) )
                              SQL> ed
                              Wrote file afiedt.buf
                              
                                1  select least(lag(avail_val, 1, sm) over (partition by decode(greatest(
                                2               AVAIL_SEATS_SEC2
                                3                      ,
                                4               AVAIL_SEATS_SEC1), AVAIL_SEATS_SEC2, trunc(DEP_DT_SEC2), trunc(
                                5                      DEP_DT_SEC1 ))
                                6               order by decode( least( AVAIL_SEATS_SEC2, AVAIL_SEATS_SEC1),
                                7                      AVAIL_SEATS_SEC2,
                                8               DEP_DT_SEC2, DEP_DT_SEC1)), sm) min_val,
                                9         avail_val,
                               10         DEP_DT_SEC1,
                               11         DEP_DT_SEC2,
                               12         AVAIL_SEATS_SEC1,
                               13         AVAIL_SEATS_SEC2,
                               14         flight_no_sec1,
                               15         flight_no_sec2
                               16  from   (select DEP_DT_SEC1,
                               17                 DEP_DT_SEC2,
                               18                 AVAIL_SEATS_SEC1,
                               19                 AVAIL_SEATS_SEC2,
                               20                 sm,
                               21                 gr,
                               22                 greatest(gr - sum(sm) over (partition by decode(greatest(
                               23                               AVAIL_SEATS_SEC2,
                               24                               AVAIL_SEATS_SEC1),
                               25                               AVAIL_SEATS_SEC2, trunc(DEP_DT_SEC2), trunc(
                               26                               DEP_DT_SEC1))
                               27                               order
                               28                               by decode(
                               29                               least ( AVAIL_SEATS_SEC2, AVAIL_SEATS_SEC1),
                               30                               AVAIL_SEATS_SEC2,
                               31                               DEP_DT_SEC2,
                               32                               DEP_DT_SEC1) ), 0) avail_val,
                               33                 flight_no_sec1,
                               34                 flight_no_sec2
                               35          from   (select DEP_DT_SEC1,
                               36                         DEP_DT_SEC2,
                               37                         AVAIL_SEATS_SEC1,
                               38                         AVAIL_SEATS_SEC2,
                               39                         flight_no_sec1,
                               40                         flight_no_sec2,
                               41                         greatest(aVAIL_SEATS_SEC2, AVAIL_SEATS_SEC1) gr,
                               42                         least(aVAIL_SEATS_SEC2, AVAIL_SEATS_SEC1)    sm
                               43*                 from   test_seats1) t1)
                              SQL> /
                              
                                 MIN_VAL  AVAIL_VAL DEP_DT_SE DEP_DT_SE AVAIL_SEATS_SEC1 AVAIL_SEATS_SEC2 FLIG FLIG
                              ---------- ---------- --------- --------- ---------------- ---------------- ---- ----
                                       3          8 24-APR-12 25-APR-12               11                3 0788 0135
                                       7          1 24-APR-12 25-APR-12               11                7 0788 0137
                                       1          0 24-APR-12 25-APR-12               11                1 0788 0137
                                       5          5 26-APR-12 27-APR-12                5               10 0790 0135
                                       5          0 26-APR-12 27-APR-12                7               10 0791 0135
                                       0          0 26-APR-12 27-APR-12                2               10 0792 0135
                              
                              6 rows selected.
                              • 12. Re: query
                                Paulie
                                babaravi wrote:
                                Paulie,
                                your table structure strange - what, exactly are sec1 and sec2?
                                Flight segments?
                                It is sector. assume a person want to travel from BOMBAY To LONDON.
                                Sector1
                                Bombay to dubai.
                                Sector2
                                Dubai to London.
                                Basically. i am trying to find out the seat availability.
                                Well, what I would do is have a SECTOR table and then a FLIGHT table - you appear to breaking the
                                rule of normalisation of not having repeating elements in your table definitions.

                                What happens if you have someone travelling from somewhere in Northern Laos to somewhere in
                                the frozen wastes of northern Canada? You could have way more than two sectors - what do you
                                do then? Bush to Vientiane-->Bangkok-->Frankfurt-->JFK-->Montreal-->Tundra. That's 6 SECTORs
                                or LEGS as we used to call them.

                                My logic was flawed - what you are trying to do is to the get the minimum availability per flight and
                                not per leg - I would definitely redesign my database logic. I would have a trigger on my SECTOR
                                table to update my FLIGHT table and subtract the necessary places on the FLIGHT for the
                                SECTOR update - check out this http://www.slideshare.net/NarendranThangarajan/airline-reservation-system-db-design
                                and google airline database schema sample.

                                But I'd be interested in trying to do this anyway - I'll keep an eye on the thread. ;)

                                Paul...
                                • 13. Re: query
                                  Stew Ashton
                                  OK, here's a try:
                                  select * from test_seats1
                                  model
                                  dimension by (
                                    row_number() over(order by dep_dt_sec1, flight_no_sec1, dep_dt_sec2, flight_no_sec2) rn
                                  )
                                  measures(
                                    dep_dt_sec1 d1, flight_no_sec1 f1, dep_dt_sec2 d2, flight_no_sec2 f2,
                                    avail_seats_sec1 as1, avail_seats_sec2 as2, 0 min_val, 0 avail_val
                                  )
                                  rules automatic order(
                                    min_val[any] = least(
                                      as1[cv()], as2[cv()], 
                                      case 
                                      when f1[cv()] = f1[cv()-1] or f2[cv()] = f2[cv()-1] 
                                        then avail_val[cv()-1] 
                                      else as2[cv()] end
                                    ),
                                    avail_val[any] = 
                                      case 
                                      when f1[cv()] = f1[cv()-1] or f2[cv()] = f2[cv()-1] 
                                        then avail_val[cv()-1] - min_val[cv()]
                                      when f1[cv()] = f1[cv()+1]
                                        then as1[cv()] - min_val[cv()]
                                      when f2[cv()] = f2[cv()+1]
                                        then as2[cv()] - min_val[cv()]
                                      else greatest(as1[cv()],as2[cv()]) - min_val[cv()] end
                                  );
                                  
                                  RN D1                 F1   D2                 F2   AS1 AS2 MIN_VAL AVAIL_VAL
                                  -- ------------------ ---- ------------------ ---- --- --- ------- ---------
                                   1 2012/04/24 15:20   0788 2012/04/25 09:50   0135  11   3       3         8 
                                   2 2012/04/24 15:20   0788 2012/04/25 15:45   0137  11   7       7         1 
                                   3 2012/04/24 15:20   0788 2012/04/25 20:45   0137  11   1       1         0 
                                   4 2012/04/26 02:20   0790 2012/04/27 10:50   0135   5  10       5         5 
                                   5 2012/04/26 10:20   0791 2012/04/27 10:50   0135   7  10       5         0 
                                   6 2012/04/26 18:20   0792 2012/04/27 10:50   0135   2  10       0         0
                                  Now, here's my interpretation of your logic:
                                  - Sometimes it's the first flight that's repeated (as in lines 1-3) and sometimes it's the second (as in lines 4-6)
                                  - You need to start over when both flight numbers are different from the previous line
                                  - MIN_VAL is the least of AS1, AS2 - and the previous line's AVAIL_VAL if we're not starting over.
                                  - AVAIL_VAL subtracts this line's MIN_VAL from either AS1, AS2 or the previous line's AVAIL_VAL.

                                  Why a MODEL clause? An analytical function might have worked, but there would be more than one pass over the table, which would be slower.
                                  • 14. Re: query
                                    Solomon Yakobson
                                    You can save one iteration:
                                    select  *
                                      from  test_seats1
                                      model
                                        dimension by (
                                                      row_number() over(order by dep_dt_sec1,flight_no_sec1,dep_dt_sec2,flight_no_sec2) rn
                                                     )
                                        measures(
                                                 dep_dt_sec1 d1,
                                                 flight_no_sec1 f1,
                                                 dep_dt_sec2 d2,
                                                 flight_no_sec2 f2,
                                                 avail_seats_sec1 as1,
                                                 avail_seats_sec2 as2,
                                                 least(avail_seats_sec1,avail_seats_sec2) min_val,
                                                 abs(avail_seats_sec1 - avail_seats_sec2) avail_val
                                                )
                                       rules automatic order(
                                                             min_val[rn > 1] = least(
                                                                                     as1[cv()],
                                                                                     as2[cv()], 
                                                                                     case 
                                                                                       when f1[cv()] = f1[cv()-1] or f2[cv()] = f2[cv()-1] 
                                                                                         then avail_val[cv()-1] 
                                                                                       else as2[cv()]
                                                                                     end
                                                                                    ),
                                                             avail_val[rn > 1] = case 
                                                                                   when f1[cv()] = f1[cv()-1] or f2[cv()] = f2[cv()-1] 
                                                                                     then avail_val[cv()-1] - min_val[cv()]
                                                                                   when f1[cv()] = f1[cv()+1]
                                                                                     then as1[cv()] - min_val[cv()]
                                                                                   when f2[cv()] = f2[cv()+1]
                                                                                     then as2[cv()] - min_val[cv()]
                                                                                   else greatest(as1[cv()],as2[cv()]) - min_val[cv()]
                                                                                 end
                                                            )
                                    /
                                    
                                            RN D1                  F1   D2                  F2          AS1        AS2    MIN_VAL  AVAIL_VAL
                                    ---------- ------------------- ---- ------------------- ---- ---------- ---------- ---------- ----------
                                             1 04/24/2012 15:20:00 0788 04/25/2012 09:50:00 0135         11          3          3          8
                                             2 04/24/2012 15:20:00 0788 04/25/2012 15:45:00 0137         11          7          7          1
                                             3 04/24/2012 15:20:00 0788 04/25/2012 20:45:00 0137         11          1          1          0
                                             4 04/26/2012 02:20:00 0790 04/27/2012 10:50:00 0135          5         10          5          5
                                             5 04/26/2012 10:20:00 0791 04/27/2012 10:50:00 0135          7         10          5          0
                                             6 04/26/2012 18:20:00 0792 04/27/2012 10:50:00 0135          2         10          0          0
                                    
                                    6 rows selected.
                                    
                                    SQL> 
                                    SY.
                                    1 2 Previous Next