मेरे पास एक उपयोगकर्ता तालिका है, जहां निम्न फ़ील्ड हैं।

| id | created_at | username | 

मैं इस तालिका को फ़िल्टर करना चाहता हूं ताकि मैं उन उपयोगकर्ताओं की संख्या प्राप्त कर सकूं जो डेटाटाइम रेंज में बनाए गए हैं, जो एन अंतराल में अलग हो गए हैं। जैसे created_at वाले 2019-01-01T00:00:00 और 2019-01-02T00:00:00 के बीच में 2 अंतरालों में विभाजित होने वाले उपयोगकर्ताओं के लिए, मुझे कुछ ऐसा मिलेगा।

_______________________________
|       dt            | count |
-------------------------------
| 2019-01-01T00:00:00 |   6   |
| 2019-01-01T12:00:00 |   7   |
-------------------------------

क्या एक हिट में ऐसा करना संभव है? मैं वर्तमान में अपने Django ORM का उपयोग N दिनांक सीमाएँ बनाने और फिर N प्रश्न बनाने के लिए कर रहा हूँ, जो बहुत कुशल नहीं है।

2
Mehran 7 सितंबर 2020, 16:29

2 जवाब

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

अपने इच्छित समय उत्पन्न करें और फिर left join और एकत्रीकरण का उपयोग करें:

select gs.ts, count(u.id)
from generate_series('2019-01-01T00:00:00'::timestamp,
                     '2019-01-01T12:00:00'::timestamp,
                     interval '12 hour'
                    ) gs(ts) left join
     users u
     on u.created_at >= gs.ts and
        u.created_at < gs.ts + interval '12 hour'
group by 1
order by 1;

संपादित करें:

यदि आप पंक्तियों की संख्या निर्दिष्ट करना चाहते हैं, तो आप कुछ इसी तरह का उपयोग कर सकते हैं:

from generate_series(1, 10, 1) as gs(n) cross join lateral
     (values ('2019-01-01T00:00:00'::timestamp + (gs.n - 1) * interval '12 hour')
     ) v(ts) left join
     users u
     on u.created_at >= v.ts and
        u.created_at < v.ts + interval '12 hour'
1
Gordon Linoff 7 सितंबर 2020, 17:55

Postgres में, इसके लिए एक समर्पित कार्य है (कई अतिभारित संस्करण, वास्तव में): width_bucket().

एक अतिरिक्त कठिनाई: यह सीधे टाइप timestamp पर काम नहीं करता है। लेकिन आप इस तरह निकाले गए युग मूल्यों के साथ काम कर सकते हैं:

WITH cte(min_ts, max_ts, buckets) AS (  -- interval and nr of buckets here
   SELECT timestamp '2019-01-01T00:00:00'
        , timestamp '2019-01-02T00:00:00'
        , 2
   )
SELECT width_bucket(extract(epoch FROM t.created_at)
                  , extract(epoch FROM c.min_ts)
                  , extract(epoch FROM c.max_ts)
                  , c.buckets) AS bucket
     , count(*) AS ct
FROM   tbl t
JOIN   cte c ON t.created_at >= min_ts  -- incl. lower
            AND t.created_at <  max_ts  -- excl. upper
GROUP  BY 1
ORDER  BY 1;

खाली बाल्टियाँ (बिना पंक्तियों वाले अंतराल) बिल्कुल भी वापस नहीं की जाती हैं। आपकी टिप्पणी से लगता है कि आप ऐसा चाहते हैं।

विशेष रूप से, यह तालिका को एक बार एक्सेस करता है - जैसा कि अनुरोध किया गया था और पहले अंतराल उत्पन्न करने और फिर तालिका में शामिल होने के विपरीत (बार-बार)।

देखो:

इसमें अभी तक प्रभावी सीमाएँ शामिल नहीं हैं, केवल बकेट नंबर। वास्तविक सीमा को सस्ते में जोड़ा जा सकता है:

WITH cte(min_ts, max_ts, buckets) AS (  -- interval and nr of buckets here
   SELECT timestamp '2019-01-01T00:00:00'
        , timestamp '2019-01-02T00:00:00'
        , 2
   )
SELECT b.*
     , min_ts + ((c.max_ts - c.min_ts) / c.buckets) * (bucket-1) AS lower_bound
FROM  (
   SELECT width_bucket(extract(epoch FROM t.created_at)
                     , extract(epoch FROM c.min_ts)
                     , extract(epoch FROM c.max_ts)
                     , c.buckets) AS bucket
        , count(*) AS ct
   FROM   tbl t
   JOIN   cte c ON t.created_at >= min_ts  -- incl. lower
               AND t.created_at <  max_ts  -- excl. upper
   GROUP  BY 1
   ORDER  BY 1
   ) b, cte c;

अब आप परिणामों को समायोजित करने के लिए केवल CTE में इनपुट मान बदलते हैं।

db<>fiddle यहां

1
Erwin Brandstetter 7 सितंबर 2020, 17:58