मैं एडब्ल्यूएस एथेना से इस घटना ट्रैकर डाटासेट चालू करने की जरूरत है

timestamp      id     event
1577863551     1      home
1577863555     1      profile
1577863555     2      home

मासिक सक्रिय उपयोगकर्ता या अद्वितीय उपयोगकर्ता में जो 30-दिन पहले से अब तक सक्रिय है। उदाहरण के लिए

date        MAU
2/1/2020    2000
2/2/2020    2500

2000 एमएयू का मतलब है कि 1/3/2020 से 2/1/2020 तक 2000 अद्वितीय उपयोगकर्ता सक्रिय हैं।
active २५०० एमएयू का मतलब है कि १/४/२०२० से २/२/२०२० तक २५०० अद्वितीय उपयोगकर्ता सक्रिय हैं

0
OctavianWR 17 नवम्बर 2020, 12:08

1 उत्तर

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

यह बल्कि जटिल है। count(distinct) over इतना आसान होगा! लेकिन यहाँ विचार है।

जब उपयोगकर्ता की गणना की जाती है तो आप अवधि प्राप्त करना चाहते हैं। विचार एक user_inc उत्पन्न करना है जो 1 है जब उपयोगकर्ता गिनना शुरू करता है और -1 जब यह रुक जाता है।

लेकिन यह गिनती आसान नहीं है। इन अवधियों के लिए 1 और -1` के झंडे के साथ - जब उपयोगकर्ता की गिनती शुरू होती है और गिनती बंद हो जाती है (31 दिन जोड़कर) पंक्तियों को जोड़ने का विचार है। फिर एक संचयी राशि निर्धारित करती है कि उस तिथि पर किसी उपयोगकर्ता की गतिविधि थी या नहीं। फ़िल्टरिंग से गतिविधि के साथ अवधियों की पहली और आखिरी पंक्तियाँ मिलती हैं।

तो, ऐसा दिखता है:

with t as (
      select id, dte, sum(sum(inc)) over (partition by id order by dte) as running_ins
      from ((select id, date(from_unixtime(timestamp)) as dte, 1 as inc
             from event_tracker
            ) union all
            (select id, date(from_unixtime(timestamp)) + interval '31' day as dte, -1 as inc
             from event_tracker
            )
           ) id
      group by id, dte
     ),
     first_last as (
      select id, dte, (case when running_ins > 0 then 1 else -1 end) as user_inc
      from (select t.*,
                   lag(running_ins) over (partition by id order by dte) as prev_running_ins
            from t
           ) t
      where prev_running_ins is null or
            prev_running_ins = 0 and running_ins > 0 or
            prev_running_ins > 0 and running_ins = 0
     )
select fl.dte,
       sum(sum(user_inc)) over (order by fl.dte) as distinct_30_days
from first_last fl
group by fl.dte;

यहां एक db<>Fiddle Postgres का उपयोग कर रहा है।

2
OctavianWR 19 नवम्बर 2020, 02:25