Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Mar 2012
    Posts
    16

    Question Unanswered: Sql to get a range type

    Hi all,

    I need to get the range type of the table A according to a value in table B.
    For example, the value 125 ( Table B) is less than 250 then return the range type 1.
    The value 350 is between 300 and 400 then return to 3.

    Code:
    Table A:
    Range Type Field:  Range Limit Value:
    1                       250                        ==> (   0 to 250)
    2                       300                        ==> (251 to 300)
    3                       400                         ==> (301 to 400)
    
    
    Table B:
    Value:
    125
    350
    270
    140
    
    Result for join Table B with Table A:
    Generic Value:    Range type returned:
    125                 1
    350                 3
    270                 2
    140                 1
    How can I write this sql code ? I'm trying using left join without success.

    Thanks,
    Davis

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I expect Tonkuma may offer a more elegant solution, but here's a quick'n'dirty one:

    select b.generic_value, (select min(a.range_type) from a where a.range_limit >= b.generic_value) as range_type from b

  3. #3
    Join Date
    Mar 2012
    Posts
    16

    Talking

    It's not dirty and works fine!
    Thanks!!!!!

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is another example.

    Good point of this example is that it is not using correlated subquery.
    Generally speaking, correlated subquery may be not so efficient than non-correlated subquery.
    But, cardinarity of table A in this problem would be small, then the difference might be not so significant.

    An issue of this example might be that the result sequence was ordered by range_type (not input sequence of table B).
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     A(Range_Type , Range_Limit) AS (
    VALUES
      ( 1 , 250 )
    , ( 2 , 300 )
    , ( 3 , 400 )
    )
    , B(generic_value) AS (
    VALUES
      125
    , 350
    , 270
    , 140
    )
    SELECT generic_value
         , Range_Type
     FROM (SELECT A.*
                , LAG(Range_Limit + 1 , 1 , 0)
                     OVER( ORDER BY Range_Type ) AS Lower_Limit
            FROM  A
          ) A
     INNER JOIN
           B
      ON   b.generic_value BETWEEN a.Lower_Limit AND a.Range_Limit
    ;
    ------------------------------------------------------------------------------
    
    GENERIC_VALUE RANGE_TYPE 
    ------------- -----------
              125           1
              140           1
              270           2
              350           3
    
      4 record(s) selected.

  5. #5
    Join Date
    Nov 2011
    Posts
    334
    Tonkuma always offer a more elegant solution.
    Mr Tonkuma, would you please recommend any books or references for me,
    I hope one day i can write elegant sqls like you

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    fengsun2,
    That is a little difficult to answer.

    I had studied general concepts of relational Database by C. J. Date's "An Introduction to Database Systems",
    DB2 practice by manuals of DB2 on maiflame.
    Later, I studied more SQL techniques(mainly it's set oriented and declarative aspects)
    by Joe Celko's books "SQL for Smarties", "SQL Puzzles and Answers", so on...
    Same time, I studied SQL techniques by solving problems asked from many peoples inside/outside IBM and through developments in some projects.
    Forums are good source for my study of SQL, in past and now.

    But, those books are old. There may be better books now(I don't know so much about books these days).
    Especially, many problems can be solved more elegantly and efficiently by using newer functionalities, like
    OLAP specifications, LISTAGG function, common-table-expression, MERGE statement, data-change-table-reference, so on...
    For example: OLAP specifications is useful to replace many of self-join or multiple references to same table in a query(including problems in Joe Celko's early books).

  7. #7
    Join Date
    Nov 2011
    Posts
    334
    Thanks a lot for your replay。
    I intend to go first to read these books you metioned above。
    I found they already have the new version。

  8. #8
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by fengsun2 View Post
    Thanks a lot for your replay。
    I intend to go first to read these books you metioned above。
    I found they already have the new version。
    May I suggest a small puzzle to start with, perhaps someone can add more puzzles:

    Identify intervals in a series of numbers

    Code:
    with t(n) as ( values 1,2,3,5,6,8,10,11,12 ) select n from t"
    
    N          
    -----------
              1
              2
              3
              5
              6
              8
             10
             11
             12
    expected output

    Code:
    with t(n) as ( values 1,2,3,5,6,8,10,11,12 ) select ..."
    
    N          M          
    ----------- -----------
              1           3
              5           6
              8           8
             10          12
    Try first solving the puzzle with only sql92 constructions, then figure out how to do it with OLAP functions
    --
    Lennart

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I have two solutions using OLAP specifications, now.

    One is short.

    Another is rather complex, but it may be slightly efficient.
    Access path showed one sort and scan the result to filter out unnecesary rows.
    If there was an index on column n, no sort was required and scan the index to filter out unnecesary rows.

  10. #10
    Join Date
    Nov 2011
    Posts
    334
    Quote Originally Posted by lelle12 View Post
    May I suggest a small puzzle to start with, perhaps someone can add more puzzles:

    Identify intervals in a series of numbers

    Code:
    with t(n) as ( values 1,2,3,5,6,8,10,11,12 ) select n from t"
    
    N          
    -----------
              1
              2
              3
              5
              6
              8
             10
             11
             12
    expected output

    Code:
    with t(n) as ( values 1,2,3,5,6,8,10,11,12 ) select ..."
    
    N          M          
    ----------- -----------
              1           3
              5           6
              8           8
             10          12
    Try first solving the puzzle with only sql92 constructions, then figure out how to do it with OLAP functions
    sorry,
    I cannot understand the expected output?
    can you explain more eleberately?
    what is the meaning of the N,M?

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The problem is to find consecutive sequences of integer numbers.

    The results are
    (1, 2, 3) , (5, 6) , (8) , (10, 11, 12)

    Then show lowest number(N) and highest number(M) in each set of sequences.
    Code:
    N          M          
    ----------- -----------
              1           3
              5           6
              8           8
             10          12

  12. #12
    Join Date
    Nov 2011
    Posts
    334
    It is an old thread ,but i did not forget it,
    now i have two answers for it after i have learned sql for a while:
    one is with olap functions:
    Code:
    with t(n) as ( values 1,2,3,5,6,8,10,11,12 ) 
    select min(n),max(n)  
    from  
    (
        select row_number() over (order by n) as rn, n
        from t 
    ) group by n-rn;
    another is not
    Code:
    with t(n) as ( values 1,2,3,5,6,8,10,11,12 )
        ,t2(n1,n2 ) as (
       select * from ( 
          select coalesce(a.n,-1) as n1, coalesce(min(b.n),100) n2
          from t a full join t b 
          on a.n < b.n 
          group by a.n ) 
       where n2 - n1 > 1 )
    select a.n2,min(b.n1) from t2 a, t2 b
    where a.n2 <= b.n1
    group by a.n2

  13. #13
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by fengsun2 View Post
    It is an old thread ,but i did not forget it,
    now i have two answers for it after i have learned sql for a while:
    one is with olap functions:
    Code:
    with t(n) as ( values 1,2,3,5,6,8,10,11,12 ) 
    select min(n),max(n)  
    from  
    (
        select row_number() over (order by n) as rn, n
        from t 
    ) group by n-rn;
    another is not
    Code:
    with t(n) as ( values 1,2,3,5,6,8,10,11,12 )
        ,t2(n1,n2 ) as (
       select * from ( 
          select coalesce(a.n,-1) as n1, coalesce(min(b.n),100) n2
          from t a full join t b 
          on a.n < b.n 
          group by a.n ) 
       where n2 - n1 > 1 )
    select a.n2,min(b.n1) from t2 a, t2 b
    where a.n2 <= b.n1
    group by a.n2

    Well done. It is a clever trick to group by the difference in value and order (first solution). Heres an alternative non-olap solution

    Code:
    with t(n) as ( values 1,2,3,5,6,8,10,11,12 )
    select min(n), m
    from (
      select t1.n, min(t2.n) as m
      from t t1
      join t t2
        on t2.n >= t1.n
      where not exists (
         select 1 from t t3
         where t3.n = t2.n + 1
      )
      group by t1.n
    ) group by m;
    --
    Lennart

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I have two solutions using OLAP specifications, now.

    One is short.

    Another is rather complex, but it may be slightly efficient.
    Access path showed one sort and scan the result to filter out unnecesary rows.
    If there was an index on column n, no sort was required and scan the index to filter out unnecesary rows.
    When I wrote this, I had the following two queries.
    Costs are added in comments.(Example 2 showed a little smaller number.)
    Both finished about 0.01 second.

    Example 1: essentially same as fengsun2's first answer.
    Code:
    /*
    Total cost	0.04 timerons
    CPU cost	52,508.21 instructions
    I/O cost	0 I/Os
    */
    ------------------------------ Commands Entered ------------------------------
    WITH
      t(n) as (
    VALUES  1, 2, 3, 5, 6, 8,10,11,12
    )
    SELECT MIN(n) AS n
         , MAX(n) AS m
     FROM (SELECT n
                , n
                  - ROW_NUMBER()
                       OVER( ORDER BY n ) AS rank
            FROM  t
          )
     GROUP BY
           rank
     ORDER BY
           rank
    ;
    ------------------------------------------------------------------------------
    
    N           M          
    ----------- -----------
              1           3
              5           6
              8           8
             10          12
    
      4 record(s) selected.
    Example 2:
    Code:
    /*
    Total cost	0.04 timerons
    CPU cost	49,918.11 instructions
    I/O cost	0 I/Os
    */
    ------------------------------ Commands Entered ------------------------------
    WITH
      t(n) AS (
    VALUES  1, 2, 3, 5, 6, 8,10,11,12
    )
    SELECT n
         , m
     FROM (SELECT n
                , NULLIF(
                     n
                   , LAG(n , 1 , -1)
                        OVER( ORDER BY n ) + 1
                  ) AS left
                , MIN(
                     NULLIF(
                        n
                      , LEAD(n , 1 , 0)
                           OVER( ORDER BY n ) - 1
                     )
                  ) OVER( ORDER BY n
                          RANGE BETWEEN CURRENT ROW
                                    AND UNBOUNDED FOLLOWING
                        ) AS m
            FROM  t
          )
     WHERE left IS NOT NULL
     ORDER BY
           n
    ;
    ------------------------------------------------------------------------------
    
    N           M          
    ----------- -----------
              1           3
              5           6
              8           8
             10          12
    
      4 record(s) selected.
    Difference was small, so I created a table and tried with more data.

    The results were mysterious for me.
    If number of data increased, Example 2 got slower than Example 1 and costs were incresed more than Example 1,
    while access path of Example 2 was simpler than Example 1.

    So, I hesitated to publish the queries.

    Code:
    +---------+------------------------------------+----------------------------------------------+
    | Number  | Total cost  /  CPU cost        /  Elapsed time                                    |
    | of rows | (timerons)     (instructions)     (seconds)                                       |
    |         |------------------------------------+----------------------------------------------+
    |         |          Example 1                 |          Example 2                           |
    +---------+------------------------------------+----------------------------------------------+
    |       9 |     0.06 /        89,730 /  0.010  |          0.06 /             85,979 /  0.010  |
    +---------+------------------------------------+----------------------------------------------+
    |    9933 |   102.89 /    58,752,304 /  0.030  |      3,175.97 /      4,396,106,752 /  0.201  |
    +---------+------------------------------------+----------------------------------------------+
    |  999920 | 7,238.58 / 7,192,913,408 /  1.833  | 31,175,404    / 43,997,938,581,504 / 32.616  |
    +---------+------------------------------------+----------------------------------------------+
    Last edited by tonkuma; 09-15-12 at 06:20. Reason: Add " than Example 1".

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here are extractions from output of db2exfmt.

    Example 1: IXSCAN ---> SORT ---> TBSCAN ---> GRPBY ---> RETURN
    Code:
    ******************** EXPLAIN INSTANCE ********************
    
    DB2_VERSION:       09.07.5
    SOURCE_NAME:       SYSSH200
    SOURCE_SCHEMA:     NULLID
    SOURCE_VERSION:
    EXPLAIN_TIME:      2012-05-28-20.49.46.194000
    EXPLAIN_REQUESTER: DB2ADMIN
    
    ...
    
    Original Statement:
    ------------------
    SELECT MIN(n) AS n , MAX(n) AS m
    FROM
       (SELECT n , n - ROW_NUMBER() OVER(
       ORDER BY n) AS rank
       FROM findcont.t)
    GROUP BY rank
    ORDER BY rank
    
    
    Optimized Statement:
    -------------------
    SELECT Q5.$C0 AS "N", Q5.$C1 AS "M", Q5.$C2
    FROM
       (SELECT MIN(Q4.N), MAX(Q4.N), Q4.$C0
       FROM
          (SELECT (Q3.N - Q3.$C1), Q3.N
          FROM
             (SELECT Q2.N, ROW_NUMBER() OVER (ORDER BY Q2.N)
             FROM
                (SELECT Q1.N
                FROM FINDCONT.T AS Q1) AS Q2) AS Q3) AS Q4
       GROUP BY Q4.$C0) AS Q5
    ORDER BY Q5.$C2
    
    Access Plan:
    -----------
            Total Cost:             102.887
            Query Degree:           1
    
            Rows
           RETURN
           (   1)
            Cost
             I/O
             |
            9953
           GRPBY
           (   2)
           99.7486
             21
             |
            9953
           TBSCAN
           (   3)
           97.9854
             21
             |
            9953
           SORT
           (   4)
           96.2214
             21
             |
            9953
           IXSCAN
           (   5)
           73.5492
             21
             |
            9953
       INDEX: SYSIBM
     SQL120527232653110
             Q1
    Example 2: IXSCAN ---> FILTER ---> RETURN
    Code:
    ******************** EXPLAIN INSTANCE ********************
    
    DB2_VERSION:       09.07.5
    SOURCE_NAME:       SYSSH200
    SOURCE_SCHEMA:     NULLID
    SOURCE_VERSION:
    EXPLAIN_TIME:      2012-05-28-18.41.58.829003
    EXPLAIN_REQUESTER: DB2ADMIN
    
    ....
    
    Original Statement:
    ------------------
    SELECT n , m
    FROM
       (SELECT n , NULLIF(n , LAG(n , 1 , -1) OVER(
       ORDER BY n) + 1) AS left , MIN(NULLIF(n , LEAD(n , 1 , 0) OVER(
       ORDER BY n) - 1)) OVER(
       ORDER BY n RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS m
       FROM findcont.t)
    WHERE left IS NOT NULL
    ORDER BY n
    
    
    Optimized Statement:
    -------------------
    SELECT Q5.N AS "N", Q5.$C2 AS "M"
    FROM
       (SELECT Q4.N, Q4.$C1, MIN(Q4.$C2) OVER (ORDER BY Q4.N RANGE BETWEEN
               CURRENT ROW AND UNBOUNDED FOLLOWING)
       FROM
          (SELECT Q3.N,
          CASE
          WHEN (Q3.N = (Q3.$C1 + 1))
          THEN NULL
          ELSE Q3.N END ,
          CASE
          WHEN (Q3.N = (Q3.$C2 - 1))
          THEN NULL
          ELSE Q3.N END
          FROM
             (SELECT Q2.N, LAG(Q2.N, Q2.$C1, Q2.$C2, Q2.$C3) OVER (ORDER BY Q2.N
                     ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING), LEAD(Q2.N, Q2.$C1,
                     Q2.$C3, Q2.$C3) OVER (ORDER BY Q2.N ROWS BETWEEN 1 FOLLOWING
                     AND 1 FOLLOWING)
             FROM
                (SELECT Q1.N, 1, -1, 0
                FROM FINDCONT.T AS Q1) AS Q2) AS Q3) AS Q4) AS Q5
    WHERE Q5.$C1 IS NOT NULL
    ORDER BY Q5.N
    
    Access Plan:
    -----------
            Total Cost:             3175.97
            Query Degree:           1
    
            Rows
           RETURN
           (   1)
            Cost
             I/O
             |
           9554.88
           FILTER
           (   2)
           3175.97
             21
             |
            9953
           IXSCAN
           (   3)
           73.5492
             21
             |
            9953
       INDEX: SYSIBM
     SQL120527232653110
             Q1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •