Question on 'Left outer join'

Moderator: NorbertKrupa

dazz
Newbie
Newbie
Posts: 12
Joined: Sat May 26, 2012 3:26 pm

Re: Question on 'Left outer join'

Post by dazz » Sat Jun 02, 2012 5:09 pm

Hi sKwa,

Thanks for the information provided, I was able to succesfully test the first_value Analytic Function separately.

However I ended up adding the following (to the original sql in the previous thread):
inner join (select cpp.owning_acct_no,cpp.serv_no, cpp.product_code ,MAX(cpp.inst_date) as dt
from cis_prod.cst_customer_product cpp
group by cpp.owning_acct_no,cpp.serv_no,cpp.product_code) c
on c.owning_acct_no = cp.owning_acct_no
AND c.SERV_NO = cp.SERV_NO
AND c.PRODUCT_CODE = cp.PRODUCT_CODE
AND c.dt = cp.INST_DATE
This seemed to eliminate the duplicates.

I did do some tests with the first_value though..
e.g.
For the first test, I added the following to the original
inner join (select distinct cpp.product_code,cpp.serv_no, cpp.owning_acct_no, FIRST_VALUE(cpp.inst_date)
OVER (PARTITION BY cpp.owning_acct_no, cpp.serv_no, cpp.product_code ORDER BY cpp.inst_date DESC)
from cis_prod.cst_customer_product as cpp) c
on c.owning_acct_no = cp.owning_acct_no
AND c.SERV_NO = cp.SERV_NO
AND c.PRODUCT_CODE = cp.PRODUCT_CODE
AND c.INST_DATE = cp.INST_DATE
However I got the error c.INST_DATE does not exist.. and I didn't know how to give "FIRST_VALUE(cpp.inst_date)" a name

For the second test, I changed the top of the sql to:
select cp.product_code, sp.product_description, cp.unique_serv_id, cp.serv_no,
cp.owning_acct_no, cp.personal_rental, cp.cust_prod_no,
FIRST_VALUE(cp.inst_date)
OVER (PARTITION BY cp.owning_acct_no, cp.serv_no, cp.product_code ORDER BY cp.inst_date DESC), cp.price_var_exist, cp.cust_prod_status,
nm.first_name, nm.surname, nm.company_name,
fpr.fpr_bill_year, fpr.fpr_bill_month, fpr.fpr_rental_charge
This gave me the correct inst_date, but it duplicated lines as follows:
105036590000 4440270 8641 4/9/2009 W ....
105036590000 4440270 8641 4/9/2009 Z ....

Not sure if this was how you meant for it to be used.

Best Regards!

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Question on 'Left outer join'

Post by id10t » Sun Jun 03, 2012 10:20 am

Hi dazz!
[...]This gave me the correct inst_date, but it duplicated lines as follows:...
Analytic functions on each record returns value, think how you can eliminate duplicates.
Why in inner join sub-query you used in distinct while in the "top" did not?
Also you can define range to work for analytic functions.


PS As you defined a problem you need FIST_VALUE function.
May be I'm wrong, i'm not expert, but it's a forum may be someone else will help you.

What gives you this query?

Code: Select all

SELECT distinct
       cp.product_code,
       sp.product_description,
       cp.unique_serv_id,
       cp.serv_no,
       cp.owning_acct_no,
       cp.personal_rental,
       cp.cust_prod_no,
       FIRST_VALUE(cp.inst_date) OVER (PARTITION BY cp.owning_acct_no, cp.serv_no, cp.product_code ORDER BY cp.inst_date DESC),
       cp.price_var_exist,
       cp.cust_prod_status,
       nm.first_name,
       nm.surname,
       nm.company_name,
       fpr.fpr_bill_year,
       fpr.fpr_bill_month,
       fpr.fpr_rental_charge
FROM   cis_prod.cst_customer_product AS cp
       LEFT OUTER JOIN
       cis_prod.cst_name AS nm
       ON cp.owning_acct_no = nm.acct_no
       LEFT OUTER JOIN
       cis_prod.sys_product AS sp
       ON cp.product_code = sp.product_code
       LEFT OUTER JOIN
       (SELECT *
        FROM   cis_prod.cst_full_period_rentals AS fp
        WHERE  fp.fpr_bill_year = '2012'
               AND fp.fpr_bill_month = '02') AS fpr
       ON cp.owning_acct_no = fpr.fpr_owning_acct_no
          AND cp.cust_prod_no = fpr.fpr_cust_prod_no
          AND cp.product_code = fpr.fpr_product_code
WHERE  (cp.product_code BETWEEN 8640 AND 8649
        OR cp.product_code BETWEEN 8660 AND 8669)
       AND nm.stop_time_stamp = '31-DEC-2000';

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Question on 'Left outer join'

Post by id10t » Sun Jun 03, 2012 6:05 pm

Hi dazz!


I finally understand your query. You need one more join as filter.
Example:
  • Let "LeftJoinQuestionTable" be as follow (LeftJoinQuestionTable its your question query with joins):

    Code: Select all

    create table LeftJoinQuestionTable
    (
        col1 varchar,   -- owning_acct_no
        col2 varchar,   -- serv_no
        col3 varchar,   -- product_code
        col4 varchar,   -- personal_rental
        col5 varchar,   -- unique_serv_id
        date date
    );
    
  • Data (here 4 partitions by col1, col2, col3):

    Code: Select all

    copy LeftJoinQuestionTable from stdin direct delimiter ',';
    acc#1,sev#1,prod#1,person A,uniq 1,2000-01-01
    acc#1,sev#1,prod#1,person B,uniq 2,2000-01-02
    acc#1,sev#1,prod#1,person C,uniq 3,2000-01-03
    acc#2,sev#1,prod#1,person D,uniq 4,2001-03-21
    acc#2,sev#1,prod#1,person E,uniq 5,2001-02-02
    acc#2,sev#1,prod#1,person F,uniq 6,2001-01-03
    acc#1,sev#2,prod#2,person G,uniq 7,2010-01-01
    acc#1,sev#2,prod#2,person H,uniq 8,2011-01-02
    acc#1,sev#2,prod#2,person I,uniq 9,2009-01-03
    acc#1,sev#2,prod#1,person J,uniq a,2002-01-01
    acc#1,sev#2,prod#1,person K,uniq b,2002-01-02
    acc#1,sev#2,prod#1,person L,uniq c,2003-01-03
    \.
  • Since I have no "CP" table I will take dates from "LeftJoinQuestionTable" (may be you will require to select all columns from partition too and use them for join. my data is simple and each date is unique too in real data only you know)

    Code: Select all

    SELECT DISTINCT First_value("date")
                      OVER (
                        PARTITION BY col1, col2, col3
                        ORDER BY "date" DESC) AS 'date'
    FROM LeftJoinQuestionTable;
    test_db=> \e
        date    
    ------------
     2000-01-03
     2001-03-21
     2003-01-03
     2011-01-02
    (4 rows)
    
    And now sub-query filter:

    Code: Select all

    SELECT col1,
           col2,
           col3,
           col4,
           col5,
           Dates."date"
    FROM   LeftJoinQuestionTable
           INNER JOIN (SELECT DISTINCT First_value("date")
                                         OVER (
                                           PARTITION BY col1, col2, col3
                                           ORDER BY "date" DESC) AS 'date'
                       FROM   LeftJoinQuestionTable) AS Dates
                   ON leftjoinquestiontable.date = Dates.date;
    Output:

    Code: Select all

      col1  | col2  |  col3  |   col4   |  col5  |    date    
    -------+-------+--------+----------+--------+------------
     acc#1 | sev#1 | prod#1 | person C | uniq 3 | 2000-01-03
     acc#1 | sev#2 | prod#1 | person L | uniq c | 2003-01-03
     acc#1 | sev#2 | prod#2 | person H | uniq 8 | 2011-01-02
     acc#2 | sev#1 | prod#1 | person D | uniq 4 | 2001-03-21
    
PS You can change FIRST_VALUE with function MAX, i.e.

Code: Select all

SELECT DISTINCT Max("date")
                  OVER (
                    PARTITION BY col1, col2, col3
                    ORDER BY "date"
                    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 'date'
FROM   LeftJoinQuestionTable;

    date    
------------
 2000-01-03
 2001-03-21
 2003-01-03
 2011-01-02

dazz
Newbie
Newbie
Posts: 12
Joined: Sat May 26, 2012 3:26 pm

Re: Question on 'Left outer join'

Post by dazz » Fri Jun 08, 2012 4:06 am

Hi skwa!

Thanks for all the info on First_value! Its great to see this working the way I wanted it to. (..I didnt even realise you posted this, but just came back to thank you for all the help already given)

Thanks again!

Post Reply

Return to “New to Vertica”