问题描述:

I have a table that shows customer_id, product_id, browse_date, purchase_date, and the difference between the browse and purchase date. It looks something like this.

id pID b_Date p_Date Diff

1 001 7/20/2014 7/20/2014 0

1 001 7/20/2014 7/20/2014 0

1 002 7/20/2014 7/20/2014 0

2 001 7/20/2014 7/20/2014 0

2 001 7/20/2014 8/01/2014 -12

2 002 7/25/2014 8/01/2014 -8

2 002 7/26/2014 8/01/2014 -7

2 002 7/28/2014 8/01/2014 -5

2 002 7/28/2014 8/01/2014 -5

I'm trying to find how many days in advanced the customer started browsing a particular product before a purchase.

However, in the case of customer 2 he made two purchases. Now the browse he made on 7/20/2014 shouldn't be counted as a browse he made for the purchase on 8/1/2014 because that was in relation to the purchase on 7/20.

I'm wondering how I would get the MIN Diff for each customer where the browse occured after the last purchase. Also, I can't use pID (product id) in the query.

I'm using teradata 13.1

网友答案:

Your question is a bit vague to me but I think you are saying that you want the minimum diff for a given purchase by customer id irrespective of pID. Here is the workup and subsequent query that accomplishes that.

Here are the results based on the dataset you provided:

id  Max_b_Date  p_Date      Min_Diff
1   2014-07-20  2014-07-20      0
2   2014-07-20  2014-07-20      0
2   2014-07-28  2014-08-01      -5

and the code I used to recreate your question in Teradata 13.1

CREATE MULTISET TABLE danf.table1
    (
    id INTEGER NOT NULL,
    pID VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
    b_Date DATE FORMAT 'MM/DD/YYYY' ,
    p_Date DATE FORMAT 'MM/DD/YYYY' ,
    Diff INTEGER 
    )
PRIMARY INDEX ( id );

INSERT INTO danf.table1 (id, pID, b_Date, p_Date, Diff) VALUES ( 1, '001', CAST('07/20/2014' AS DATE FORMAT 'MM/DD/YYYY'), CAST('07/20/2014' AS DATE FORMAT 'MM/DD/YYYY'), 0 );
INSERT INTO danf.table1 (id, pID, b_Date, p_Date, Diff) VALUES ( 1, '001', CAST('07/20/2014' AS DATE FORMAT 'MM/DD/YYYY'), CAST('07/20/2014' AS DATE FORMAT 'MM/DD/YYYY'), 0 );
INSERT INTO danf.table1 (id, pID, b_Date, p_Date, Diff) VALUES ( 1, '002', CAST('07/20/2014' AS DATE FORMAT 'MM/DD/YYYY'), CAST('07/20/2014' AS DATE FORMAT 'MM/DD/YYYY'), 0 );
INSERT INTO danf.table1 (id, pID, b_Date, p_Date, Diff) VALUES ( 2, '001', CAST('07/20/2014' AS DATE FORMAT 'MM/DD/YYYY'), CAST('07/20/2014' AS DATE FORMAT 'MM/DD/YYYY'), 0 );
INSERT INTO danf.table1 (id, pID, b_Date, p_Date, Diff) VALUES ( 2, '001', CAST('07/20/2014' AS DATE FORMAT 'MM/DD/YYYY'), CAST('08/01/2014' AS DATE FORMAT 'MM/DD/YYYY'), -12 );
INSERT INTO danf.table1 (id, pID, b_Date, p_Date, Diff) VALUES ( 2, '002', CAST('07/25/2014' AS DATE FORMAT 'MM/DD/YYYY'), CAST('08/01/2014' AS DATE FORMAT 'MM/DD/YYYY'), -8 );
INSERT INTO danf.table1 (id, pID, b_Date, p_Date, Diff) VALUES ( 2, '002', CAST('07/26/2014' AS DATE FORMAT 'MM/DD/YYYY'), CAST('08/01/2014' AS DATE FORMAT 'MM/DD/YYYY'), -7 );
INSERT INTO danf.table1 (id, pID, b_Date, p_Date, Diff) VALUES ( 2, '002', CAST('07/28/2014' AS DATE FORMAT 'MM/DD/YYYY'), CAST('08/01/2014' AS DATE FORMAT 'MM/DD/YYYY'), -5 );
INSERT INTO danf.table1 (id, pID, b_Date, p_Date, Diff) VALUES ( 2, '002', CAST('07/28/2014' AS DATE FORMAT 'MM/DD/YYYY'), CAST('08/01/2014' AS DATE FORMAT 'MM/DD/YYYY'), -5 );

SELECT 
    t1.id
    ,MAX(b_Date) AS Max_b_Date
    ,t1.p_Date
    ,MAX(t1.Diff) AS Min_Diff
FROM danf.table1 t1
GROUP BY 
    t1.id, 
    t1.p_Date;
相关阅读:
Top