मैं एक प्रश्न लिखने की कोशिश कर रहा हूं जो उपयोगकर्ता लेनदेन तालिका से 'सदस्यता गतिविधि' को परिभाषित करता है और फिर उसका वर्णन करता है। मान लें कि जिस लेन-देन तालिका से हम काम कर रहे हैं, उसमें customer_id, create_at (लेन-देन की तारीख), मर्चेंट_नाम, transaction_amount है।

अंतिम आउटपुट तालिका में पहले कॉलम के रूप में customer_Id, दूसरे कॉलम के रूप में activity_month, तीसरे कॉलम के रूप में Merchant_name_x और चौथे कॉलम के रूप में _merchant_name_y होना चाहिए (विभिन्न व्यापारियों के लिए तब से अनंत कॉलम हो सकते हैं)। और यहां मुश्किल हिस्सा है: तालिका को '1' के साथ पॉप्युलेट किया जाना चाहिए यदि वह सेल उस उपयोगकर्ता के कम से कम 3 लगातार महीनों की स्ट्रिंग का प्रतिनिधित्व करता है उस व्यापारी के साथ कम से कम एक बार लेनदेन करता है, और अन्यथा '0' .

यहां मूल कोड है जो मुझे गतिविधि के लिए '1' और बिना किसी गतिविधि के '0' के साथ आउटपुट तालिका की तरह दिखता है। इसे संशोधित करने की आवश्यकता है ताकि '1' केवल तभी दिखाई दे, जब यह दिए गए व्यापारी के लिए >= 3 महीने की लगातार गतिविधि के अटूट स्ट्रिंग का हिस्सा हो और अन्यथा '0'।

SELECT customer_id
    , LAST_DAY(created_at::DATE) AS month
    , MAX(CASE WHEN merchant_name = 'Amazon Prime' THEN 1 ELSE 0 END) AS amazon
    , MAX(CASE WHEN merchant_name = 'Netflix.com' THEN 1 ELSE 0 END) AS netflix
FROM TABLE
GROUP BY 1,2

आउटपुट कुछ इस तरह दिखाई देगा: '1' यह दर्शाता है कि उपयोगकर्ता ने उस व्यापारी के साथ कम से कम एक बार लेन-देन किया है (इससे कोई फर्क नहीं पड़ता कि महीने में एक बार, दो बार, या 300 बार) और यह >= 3 लगातार महीनों का हिस्सा है वह उपयोगकर्ता उस व्यापारी के साथ लेन-देन कर रहा है

CUSTOMER_ID    MONTH       AMAZON   NETFLIX
54321          2019-04-30       1         0
54321          2019-03-31       1         0
54321          2019-02-29       1         1
54321          2019-01-31       1         1
54321          2018-12-31       0         1
54321          2018-11-30       0         0  

एक टेबल से कार्य करना जो लेनदेन को सूचीबद्ध और वर्णन करता है:

ColumnsData                  Type
TRANSACTION_ID               NUMBER(38,0)
CREATED_AT                   TIMESTAMP_NTZ(9)
AMOUNT_DOLLARS   NUMBER(38,0)
CUSTOMER_ID                  NUMBER(38,0)
MERCHANT_NAME                VARCHAR(16777216)

(डेटा तालिका का एक नमूना):

TRANSACTION_ID     CREATED_AT     AMOUNT_DOLLARS   CUSTOMER_ID   MERCHANT_NAME
1234567            2018-08-23     57.31            306797979     Amazon Prime
7654321            2020-09-21     10.99            309221214     Stp & Shop
9999971            2020-01-07     11.59            509227711     Lyft Com
6549875            2019-05-10     88.23            311188226     Lttle Caesar
3121541            2020-07-31     72.01            307746845     Redbox
1279875            2020-04-05     15.20            315151515     Family Dollar

(कॉम्पैक्टनेस के लिए CREATED_AT से टाइमस्टैम्प छोड़ दिया गया है) क्वेरी को हज़ारों ग्राहकों तक ले जाना चाहिए, जिनमें से प्रत्येक कई लेनदेन कर रहा है। बहुत धन्यवाद।

0
Paul Anthony 25 अक्टूबर 2020, 22:11

1 उत्तर

सबसे बढ़िया उत्तर

मुझे लगता है कि अगर मैं सही ढंग से समझता हूं तो आपको नीचे दिए गए विंडो वाले फ़ंक्शन का उपयोग करने की आवश्यकता होगी:

अद्यतन: नमूना डेटा को दर्शाने के लिए अद्यतन किया गया। मैंने आपके द्वारा प्रदान किया गया डेटा लिया और Customer_ID = 306797979 और Merchant_name = Amazon के लिए 3 अतिरिक्त रिकॉर्ड जोड़े। परिणामों में आप जो देखेंगे वह यह है कि 2018-08 और 09 रिकॉर्ड 0 हैं क्योंकि उस समय अमेज़न सदस्यता के लगातार 3 महीने नहीं हैं। जब हमने २०१८-१० और ११ को हिट किया तो उन दोनों के पास अब बाद के ३ लेन-देन हैं।

create temporary table temp (
TRANSACTION_ID NUMBER(38,0),
CREATED_AT TIMESTAMP_NTZ(9),
AMOUNT_DOLLARS NUMBER(38,0),
CUSTOMER_ID NUMBER(38,0),
MERCHANT_NAME VARCHAR(16777216)
);

insert into temp 
    values 
    (1234567, '2018-08-23 00:00:00', 57.31, 306797979, 'Amazon Prime'),
    (7654321, '2020-09-21 00:00:00', 10.99, 309221214, 'Stp & Shop'),
    (9999971, '2020-01-07 00:00:00', 11.59, 509227711, 'Lyft Com'),
    (6549875, '2019-05-10 00:00:00', 88.23, 311188226, 'Lttle Caesar'),
    (3121541, '2020-07-31 00:00:00', 72.01, 307746845, 'Redbox'),
    (1279875, '2020-04-05 00:00:00', 15.20, 315151515, 'Family Dollar'),
    (1234567, '2018-11-23 00:00:00', 57.31, 306797979, 'Amazon Prime'),
    (1234236, '2018-09-23 00:00:00', 57.31, 306797979, 'Amazon Prime'),
    (3972831, '2018-10-23 00:00:00', 57.31, 306797979, 'Amazon Prime');

with _filler_dates as (
    select
      date_trunc('MONTH',dateadd(
        month,
        '-' || row_number() over (order by null),
        dateadd(day, 1 , current_date()))) as filler_date
    from table (generator(rowcount => 30))
),
_data as(
select customer_id 
    , merchant_name
    , amount_dollars
    , date_trunc('MONTH', CREATED_AT) as cur_month
    , COALESCE(LEAD(cur_month,1,NULL) OVER (
        PARTITION BY customer_id
        ORDER BY cur_month)
        , date_trunc('MONTH', current_date())) as next_month   
from temp)
,_merged as (
select d.customer_id
    , d.merchant_name
    , CASE WHEN fd.filler_date <> cur_month then NULL else d.amount_dollars end as amount_dollars
    , fd.filler_date  
from _data d
join _filler_dates fd
 on fd.filler_date between  d.cur_month and dateadd(MONTH, -1, d.next_month)
)

select *
    , CASE WHEN sum(CASE WHEN amount_dollars > 0 THEN 1 ELSE 0 END)
                 OVER (PARTITION BY customer_id, merchant_name
                 ORDER BY filler_date ASC
                 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) >= 3
           THEN 1 ELSE 0 END as amazon    
from _merged
order by 1,2,4;

//drop table temp;
0
Daniel Zagales 2 नवम्बर 2020, 10:59