संपादित करें मेरा बुरा, जिसे टाइमस्टैम्प 'तारीख' कहा जाता है...

हमारी डेटा तालिका में टाइमस्टैम्प, मान और डेल्टा कॉलम शामिल हैं। डेल्टा अंतिम गैर-शून्य पढ़ने के बाद से मिनटों की संख्या है।

CREATE TABLE Table1
    ("ts" timestamp with time zone, "value" numeric, "delta" int)
;

INSERT INTO Table1
    ("ts", "value", "delta")
VALUES
    ('2019-09-09 12:01:00', 3.5, NULL),
    ('2019-09-09 12:02:00', 3.2, 1),
    ('2019-09-09 12:03:00', NULL, 1),
    ('2019-09-09 12:04:00', 2.9, 2),
    ('2019-09-09 12:05:00', NULL, 1),
    ('2019-09-09 12:06:00', 3.0, 2),
    ('2019-09-09 12:07:00', NULL, 1),
    ('2019-09-09 12:08:00', NULL, 2),
    ('2019-09-09 12:09:00', NULL, 3),
    ('2019-09-09 12:10:00', NULL, 4),
    ('2019-09-09 12:11:00', 3.2, 5),
    ('2019-09-09 12:12:00', NULL, 1)
;
SELECT ts,
       value,
       delta,
  FROM table

+---------------------+-------+-------+
| ts                  | value | delta |
+---------------------+-------+-------+
| 2019-09-09 12:01:00 | 3.5   | 1     |
| 2019-09-09 12:02:00 | 3.2   | 1     |
| 2019-09-09 12:03:00 |       | 1     |
| 2019-09-09 12:04:00 | 2.9   | 2     |
| 2019-09-09 12:05:00 |       | 1     |
| 2019-09-09 12:06:00 | 3.0   | 2     |
| 2019-09-09 12:07:00 |       | 1     |
| 2019-09-09 12:08:00 |       | 2     |
| 2019-09-09 12:09:00 |       | 3     |
| 2019-09-09 12:10:00 |       | 4     |
| 2019-09-09 12:11:00 | 3.2   | 5     |
| 2019-09-09 12:12:00 |       | 1     |
+---------------------+-------+-------+

डेटा के एक सबसेट को देखते हुए, हम शून्य मानों को अंतिम गैर-शून्य मान से कैसे बदल सकते हैं यदि वह प्रतिस्थापन मान पहले से ही चुना नहीं गया है:

SELECT ts,
       value,
       delta,
  FROM table
 WHERE (/* expression giving us an arbitrary distribution of rows */)

+---------------------+-------+-------+
| ts                  | value | delta |
+---------------------+-------+-------+
| 2019-09-09 12:01:00 | 3.5   |       |
| 2019-09-09 12:03:00 |       | 1     |
| 2019-09-09 12:05:00 |       | 1     |
| 2019-09-09 12:07:00 |       | 1     |
| 2019-09-09 12:09:00 |       | 3     |
| 2019-09-09 12:11:00 | 3.2   | 5     |
+---------------------+-------+-------+

हम चाहते हैं:

+---------------------+-------+-------+
| ts                  | value | delta |
+---------------------+-------+-------+
| 2019-09-09 12:01:00 | 3.5   |       |
| 2019-09-09 12:03:00 | 3.2   | 1     |
| 2019-09-09 12:05:00 | 2.9   | 1     |
| 2019-09-09 12:07:00 | 3.0   | 1     |
| 2019-09-09 12:09:00 |       | 3     |<- an actual null
| 2019-09-09 12:11:00 | 3.2   | 5     |
+---------------------+-------+-------+

इस मामले में पंक्तियों का वितरण विषम है; हालाँकि, यह मनमाना है। अंतिम गैर-शून्य मानों का उपयोग कब और क्या करना है, यह निर्धारित करने में हम दिनांक आवृत्ति में एक कथित पैटर्न का उपयोग नहीं कर सकते हैं।

SQLFiddle

हमने अब तक क्या प्रयास किया है

पहले कदम के रूप में, सभी अंतिम मूल्यों को आगे बढ़ाएं।

WITH seq AS (
  SELECT ts,
         value,
         delta,
         ROW_NUMBER() OVER(ORDER BY date) AS row_no,
         COUNT(*) OVER() AS total_count
    FROM Table1
 ),
 val AS (
   SELECT ts,
          value,
          value_p,
          first_value(value) over (partition by value_p order by date),
          delta,
          row_no,
          total_count
     FROM (
       SELECT ts,
              value,
              delta,
              row_no,
              total_count,
              sum(case when value is null then 0 else 1 end) over
                (order by date) as value_p
         FROM seq
     ORDER BY ts
       ) as a
)
SELECT ts,
       delta,
       value,
       case when value is null then first_value else value 
       end as cf
  FROM val

|                  ts |  delta |  value |  cf |
|---------------------|--------|--------|-----|
| 2019-09-09 12:01:00 | (null) |    3.5 | 3.5 |
| 2019-09-09 12:02:00 |      1 |    3.2 | 3.2 |
| 2019-09-09 12:03:00 |      1 | (null) | 3.2 |
| 2019-09-09 12:04:00 |      2 |    2.9 | 2.9 |
| 2019-09-09 12:05:00 |      1 | (null) | 2.9 |
| 2019-09-09 12:06:00 |      2 |      3 |   3 |
| 2019-09-09 12:07:00 |      1 | (null) |   3 |
| 2019-09-09 12:08:00 |      2 | (null) |   3 |
| 2019-09-09 12:09:00 |      3 | (null) |   3 |
| 2019-09-09 12:10:00 |      4 | (null) |   3 |
| 2019-09-09 12:11:00 |      5 |    3.2 | 3.2 |
| 2019-09-09 12:12:00 |      1 | (null) | 3.2 |

जब हम डेटा उपसमुच्चय के लिए पंक्तियों को वितरित करते हैं, तो अब हमारे पास दोनों मान होते हैं और उस मान से कितनी पंक्तियाँ वापस आती हैं। जब हम WHERE के माध्यम से सबसेट उत्पन्न करते हैं, तो हम यह पता नहीं लगा सकते हैं कि यह कैसे निर्धारित किया जाए कि किसी मूल्य को आगे बढ़ाया जाए या नहीं या शून्य के रूप में छोड़ दिया जाए।

समाधान के लिए पूर्वनिर्धारित डेल्टा कॉलम की आवश्यकता नहीं होने पर बोनस अंक दिए जाते हैं।

0
Ragamffn 11 सितंबर 2019, 00:12

2 जवाब

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

sum(case when value is null then 0 else 1 end) over (order by date) as value_p का उपयोग करने का विचार अच्छा था। यह मानों को समान value_p वाले समूहों में सॉर्ट करता है।

वहां से, यदि आप date को वास्तविक टाइमस्टैम्प के रूप में देखते हैं, तो आप tsrange(min(date), max(date), '[]') का उपयोग करके तिथियों को एक साथ समूहित कर सकते हैं। सुनिश्चित करें कि श्रेणी के सिरे उन पंक्तियों को कैप्चर करने के लिए समावेशी हैं जहां समूह की शुरुआत और अंत एक ही समय है।

फिर, ऑपरेटर द्वारा निहित का उपयोग करके बस अपनी परीक्षण तिथियों में शामिल हों।

WITH test_dates(test_date) as (VALUES 
        ('2019-09-09 12:01:00'::timestamp),
        ('2019-09-09 12:03:00'),
        ('2019-09-09 12:05:00'),
        ('2019-09-09 12:07:00'),
        ('2019-09-09 12:09:00'),
        ('2019-09-09 12:11:00')
), value_ranges AS (
    SELECT tsrange(min(date)::timestamp, max(date)::timestamp, '[]') as sample_range, 
       max(value) as value, -- There's only one non-null value, this could be min
       value_p
    FROM (
       SELECT date,
       value,
       sum(case when value is null then 0 else 1 end) over
            (order by date) as value_p
       FROM table1
    ) sub 
    GROUP BY value_p
)
SELECT test_date, 
       CASE WHEN row_number() OVER (PARTITION BY value_p ORDER BY test_date) = 1 THEN value 
       ELSE null END  -- Only the first row of the group is non-null
FROM test_dates
JOIN value_ranges on test_date <@ sample_range
;

डेल्टा कॉलम की कोई आवश्यकता नहीं है।

बेला

1
Jeremy 11 सितंबर 2019, 03:57
सिर झुकाने वाला सामान। एक छोटे डेटासेट के लिए बढ़िया काम करता है, लेकिन यह महंगा है क्योंकि डेटा बढ़ता है उदा। 25,000 में से 500 पंक्तियों को खींचना = 1.5 सेकंड। यह tsrange को एक तेज़ मिनट (दिनांक), अधिकतम (दिनांक), और जॉइन ... ON test_date>= min और <= max के साथ बदलने के बाद। आगे अनुकूलित करने के लिए कोई सलाह?
 – 
Ragamffn
12 सितंबर 2019, 19:25
हां, उन में शामिल होना तेज़ होगा, जब तक कि आप तारीखों को वास्तविक टाइमस्टैम्प के रूप में संग्रहीत नहीं करते हैं, जिसकी मैं अनुशंसा करता हूं। वह सब एकत्रीकरण निश्चित रूप से महंगा होगा, लेकिन किसी और अनुकूलन की सिफारिश करने के लिए, हमें व्याख्या विश्लेषण (एक नए प्रश्न में) का आउटपुट देखना होगा।
 – 
Jeremy
12 सितंबर 2019, 21:04
तिथियां वास्तव में टाइमस्टैम्प हैं। मेरा प्रारंभिक संपादन और पहेली गलत तरीके से 'तारीख' पर वर्चर के रूप में सेट किया गया था ...
 – 
Ragamffn
12 सितंबर 2019, 21:30

अद्यतन: एहसास हुआ कि मैं नमूना संदर्भ बिंदु के बाद प्रविष्टियां खींच रहा था जब मुझे पहले प्रविष्टियों को खींचना चाहिए था। फिक्स्ड।

आपकी तालिका को देखते हुए और यह मानते हुए कि आप एक टाइमस्टैम्प्ट चाहते थे, तारीख नहीं, यह आपको वह देगा जो आप चाहते हैं। नमूनों को फैलाने के लिए पहली तालिका अभिव्यक्ति में बस minutes_between_intervals कॉलम बदलें।

मैंने सीटीई को पठनीयता में मदद करने के लिए आवश्यक होने की तुलना में कुछ अधिक वर्बोज़ छोड़ दिया है।

WITH with_offsets AS (

  -- First add in some metadata about how many minutes have elapsed since you
  -- started sampling along with a constant for the sampling interval.

  SELECT
    2 AS minutes_between_intervals, -- This is how often you're sampling
    date,
    value,
    delta,
    extract(minute FROM date - (min(date) OVER (ORDER BY date)))::integer AS minutes_offset
  FROM Table1

), with_groups AS (

  -- Add grouping, setting the sample entries as reference points and the
  -- entries leading up to it as part of its group.

  SELECT
    *,
    CASE WHEN minutes_offset % minutes_between_intervals = 0 THEN minutes_offset
         ELSE minutes_offset + (minutes_between_intervals - (minutes_offset % minutes_between_intervals))
    END AS sample_group,
    minutes_offset % minutes_between_intervals = 0 AS is_sample_boundary
  FROM with_offsets

), with_arrays AS (

  -- Then aggregate them into arrays. The values array has all NULLs
  -- removed. The groups with sample entries are marked.

  SELECT
    array_agg(date) AS dates,
    array_agg(value) FILTER (WHERE value IS NOT NULL) AS values,
    array_agg(delta) AS deltas,
    bool_or(is_sample_boundary) AS has_complete_sample
  FROM with_groups
  GROUP BY sample_group
)

-- Now take the last entry from each array, which will be the sample date,
-- the last recorded value, and the last recorded sample delta.

SELECT
  dates[array_upper(dates, 1)] AS date,
  values[array_upper(values, 1)] AS value,
  deltas[array_upper(deltas, 1)] AS delta
FROM with_arrays
WHERE has_complete_sample;
0
Miles Elam 11 सितंबर 2019, 19:31
हम पहले से नहीं जानते कि नमूनाकरण कैसे समाप्त होता है। यह डेटा के मूल पूल के आकार के आधार पर परिवर्तनशील है, और इसमें वृद्धि भी नहीं हो सकती है।
 – 
Ragamffn
12 सितंबर 2019, 21:35