मैं एक प्रश्न लिखने की कोशिश कर रहा हूं जो एक महीने के दौरान संचयी उपयोगकर्ता गिनती प्राप्त करता है।

WITH USERS_PER_DAY AS (
  SELECT 
    DATE_TRUNC('day', HOUR_DIM.UTC) DAY
  , COUNT(DISTINCT CLIENT_SID) ACTIVE_USER_COUNT
  FROM RPT.S_HOURLY_INACTIVE_TVS_AGG
  WHERE DATEDIFF('month', HOUR_DIM.UTC, CURRENT_DATE) BETWEEN 0 AND 0
  GROUP BY 
    DATE_TRUNC('day', HOUR_DIM.UTC) 
)
SELECT  
DAY,
SUM(ACTIVE_USER_COUNT) OVER (PARTITION BY APP_NAME ORDER BY DAY ASC rows between unbounded preceding and current row) CUMULATIVE_ACTIVE_USER_ACOUNT
FROM USERS_PER_DAY

आउटपुट अब इस तरह दिखता है:

enter image description here

समस्या यह है कि मुझे महीने के लिए विशिष्ट या अद्वितीय उपयोगकर्ताओं की संख्या की आवश्यकता है, लेकिन इस क्वेरी में दिनों के बीच उपयोगकर्ताओं में दोहराव है। मुझे पता है कि मैं अपने विंडो फ़ंक्शन में गिनती (विशिष्ट ...) का उपयोग नहीं कर सकता लेकिन क्या यह सुनिश्चित करने का कोई और तरीका है कि मेरे पास दिनों के बीच उपयोगकर्ताओं में दोहराव नहीं है?

1
James D. 25 मार्च 2020, 19:48

3 जवाब

इसके लिए "चतुर" दृष्टिकोण dense_rank() के योग का उपयोग करना है:

SELECT first_day, APP_NAME,
       SUM(COUNT(*)) OVER (PARTITION BY APP_NAME ORDER BY first_day ASC) as CUMULATIVE_ACTIVE_USER_ACOUNT
FROM (SELECT CLIENT_SID, APP_NAME,
             MIN(DATE_TRUNC('day', HOUR_DIM.UTC)) as first_day
      FROM RPT.S_HOURLY_INACTIVE_TVS_AGG
      WHERE DATEDIFF('month', HOUR_DIM.UTC, CURRENT_DATE) BETWEEN 0 AND 0
      GROUP BY CLIENT_SID, APP_NAME
     ) cs
GROUP BY first_day, APP_NAME;
0
Gordon Linoff 26 मार्च 2020, 00:57
आपकी प्रतिक्रिया के लिए धन्यवाद्। हालांकि, यह मेरे लिए काम नहीं कर रहा प्रतीत होता है। इसका परिणाम एक तालिका में होता है जिसमें प्रत्येक दिन के लिए 50 उपयोगकर्ता होते हैं।
 – 
James D.
25 मार्च 2020, 20:09
@ जेम्स डी। . . . मेरे पास गलत विचार था। मैंने अभी पूरी तरह से जवाब दोबारा लिखा है। मुख्य विचार प्रत्येक उपयोगकर्ता के लिए पहली तारीख प्राप्त करना और फिर उसे जोड़ना है।
 – 
Gordon Linoff
26 मार्च 2020, 01:01
यह तब तक काम करता है जब तक आपके पास प्रत्येक दिन कम से कम एक उपयोगकर्ता का first_day हो।
 – 
Simeon Pilgrim
26 मार्च 2020, 01:11

तो एक सरल समाधान यह है कि डेटा को अलग-अलग दिनों में, और प्रति दिन अलग-अलग उपयोगकर्ताओं में बदल दिया जाए, और फिर परिणाम प्राप्त करने के लिए सीटीई में शामिल हों:

WITH data AS (  
    select 
        hour_dim_utc::timestamp_ntz as hour_dim_utc
        ,user_id 
    from values
        ('2020-03-10 9:50', 1 ),
        ('2020-03-10 9:51', 3 ),
        ('2020-03-10 10:51', 3 ),
        ('2020-03-11 9:52', 1 ),
        ('2020-03-11 9:53', 2 ),
        ('2020-03-11 9:54', 0 ),
        ('2020-03-12 9:55', 0 ),
        ('2020-03-12 9:56', 1 ),
        ('2020-03-12 9:57', 3 ),
        ('2020-03-14 9:58', 2 ),
        ('2020-03-15 9:59', 3 ),
        ('2020-03-16 10:00', 2 ),
        ('2020-03-17 10:01', 2 ),
        ('2020-03-18 10:02', 0 ),
        ('2020-03-19 10:04', 11 )
         s( hour_dim_utc, user_id)
), distinct_users_days AS (
    select distinct 
        hour_dim_utc::date as day
        ,user_id
    from data
), distinct_days AS (
    select distinct 
        hour_dim_utc::date as day
    from data
)
select 
    a.day
    ,count(distinct(u.user_id)) as acum_count
from distinct_days as a
join distinct_users_days as u on u.day <= a.day
group by 1 order by 1;

देता है:

DAY         ACUM_COUNT
2020-03-10  2
2020-03-11  4
2020-03-12  4
2020-03-14  4
2020-03-15  4
2020-03-16  4
2020-03-17  4
2020-03-18  4
2020-03-19  5

अपने SQL में आप WHERE DATEDIFF('month', HOUR_DIM.UTC, CURRENT_DATE) BETWEEN 0 AND 0 करते हैं, यह कहना अधिक पठनीय और प्रदर्शनकारी होगा WHERE hour_dim.utc >= DATE_TRUNC('month', CURRENT_DATE)

0
Simeon Pilgrim 26 मार्च 2020, 01:02

गॉर्डन का अद्यतन उत्तर अच्छा है यदि आपके पास पर्याप्त डेटा है कि हर दिन, एक उपयोगकर्ता प्राप्त करें जिसके पास महीने में प्रत्येक दिन के लिए पहला दिन है, लेकिन जब डेटा मेरे उदाहरण डेटा की तरह विरल है, तो आपको अपेक्षित परिणाम नहीं मिलते हैं

गॉर्डन का कोड प्रभावी रूप से यह है:

WITH data AS (  
select hour_dim_utc::timestamp_ntz as hour_dim_utc, user_id from values
    ('2020-03-10 9:50', 1 ),
    ('2020-03-10 9:51', 3 ),
    ('2020-03-10 10:51', 3 ),
    ('2020-03-11 9:52', 1 ),
    ('2020-03-11 9:53', 2 ),
    ('2020-03-11 9:54', 0 ),
    ('2020-03-12 9:55', 0 ),
    ('2020-03-12 9:56', 1 ),
    ('2020-03-12 9:57', 3 ),
    ('2020-03-14 9:58', 2 ),
    ('2020-03-15 9:59', 3 ),
    ('2020-03-16 10:00', 2 ),
    ('2020-03-17 10:01', 2 ),
    ('2020-03-18 10:02', 0 ),
    ('2020-03-19 10:04', 11 )
     s( hour_dim_utc, user_id)
)
select 
    first_day
    ,sum(count(*)) over (ORDER BY first_day ASC) as acum 
from (
    select user_id
        ,min(hour_dim_utc::date) as first_day
    from data 
    group by 1
) group by 1;

जो देता है:

FIRST_DAY   ACUM
2020-03-10  2
2020-03-11  4
2020-03-19  5
0
Simeon Pilgrim 26 मार्च 2020, 01:15