क्या एक ही क्वेरी में प्रति {id, date} और गिनती> 1 प्रति {id, date, columnX} पंक्तियों की कुल संख्या प्राप्त करने का कोई तरीका है?

उदाहरण के लिए, ऐसी तालिका होना:

 id         date         columnX
1        2017-04-20         a
1        2017-04-20         a
1        2017-04-18         b
1        2017-04-17         c
2        2017-04-20         a
2        2017-04-20         a
2        2017-04-20         c
2        2017-04-19         b
2        2017-04-19         b
2        2017-04-19         b
2        2017-04-19         b
2        2017-04-19         c

नतीजतन, मैं निम्न तालिका प्राप्त करना चाहता हूं:

id         date       columnX         count>1    count_total  
1        2017-04-20       a              2            2
2        2017-04-20       a              2            3
2        2017-04-19       b              4            5

मैंने इसे विभाजन के साथ करने की कोशिश की लेकिन अजीब परिणाम प्राप्त हुए। मैंने सुना है कि रोलअप फ़ंक्शन का उपयोग किया जा सकता है, लेकिन ऐसा लगता है कि यह केवल लीगेसी SQL में लागू है, जो मेरे लिए विकल्प नहीं है।

0
hamsy 21 अगस्त 2017, 16:12

3 जवाब

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

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

select id, date, columnx, cnt,
       (case when cnt > 1 then cnt else 0 end) as cnt_gt_1,
       total_cnt
from (select id, date, columnx, count(*) as cnt
             sum(count(*)) over (partition by id, date) as total_cnt
      from t
      group by id, date, columnx
     ) x
where cnt > 1;
2
Gordon Linoff 21 अगस्त 2017, 16:45

एक अन्य संभावना:

SELECT
  id,
  date,
  data.columnX columnX,
  data.count_ count_bigger_1,
  count_total
FROM(
  SELECT
    id,
    date,
    ARRAY_AGG(columnX) data,
    COUNT(1) count_total
  FROM
    `your_table_name`
  GROUP BY
    id, date
  ),
UNNEST(ARRAY(SELECT AS STRUCT columnX, count(1) count_ FROM UNNEST(data) columnX GROUP BY columnX HAVING count(1) > 1)) data

आप नकली डेटा के साथ इसका परीक्षण कर सकते हैं:

WITH data AS(
  SELECT 1 AS id, '2017-04-20' AS date, 'a' AS columnX UNION ALL
  SELECT 1 AS id, '2017-04-20' AS date, 'a' AS columnX UNION ALL
  SELECT 1 AS id, '2017-04-18' AS date, 'b' AS columnX UNION ALL
  SELECT 1 AS id, '2017-04-17' AS date, 'c' AS columnX UNION ALL
  SELECT 2 AS id, '2017-04-20' AS date, 'a' AS columnX UNION ALL
  SELECT 2 AS id, '2017-04-20' AS date, 'a' AS columnX UNION ALL
  SELECT 2 AS id, '2017-04-20' AS date, 'c' AS columnX UNION ALL
  SELECT 2 AS id, '2017-04-19' AS date, 'b' AS columnX UNION ALL
  SELECT 2 AS id, '2017-04-19' AS date, 'b' AS columnX UNION ALL
  SELECT 2 AS id, '2017-04-19' AS date, 'b' AS columnX UNION ALL
  SELECT 2 AS id, '2017-04-19' AS date, 'b' AS columnX UNION ALL
  SELECT 2 AS id, '2017-04-19' AS date, 'c' AS columnX  
)

SELECT
  id,
  date,
  data.columnX columnX,
  data.count_ count_bigger_1,
  count_total
FROM(
  SELECT
    id,
    date,
    ARRAY_AGG(columnX) data,
    COUNT(1) count_total
  FROM
    data
  GROUP BY
    id, date
  ),
UNNEST(ARRAY(SELECT AS STRUCT columnX, count(1) count_ FROM UNNEST(data) columnX GROUP BY columnX HAVING count(1) > 1)) data

यह समाधान विश्लेषणात्मक कार्य से बचा जाता है (जो इनपुट के आधार पर काफी महंगा हो सकता है) और डेटा की बड़ी मात्रा में अच्छी तरह से स्केल करता है।

1
Willian Fuks 21 अगस्त 2017, 19:20

मैं आपको अपने उदाहरण में दो और पंक्तियों को जोड़ने की सलाह देता हूं

1        2017-04-20         x
1        2017-04-20         x

और जांचें कि पिछले दो उत्तरों में आपको कौन से समाधान मिलेंगे:
यह नीचे जैसा कुछ होगा:

id         date       columnX         count>1    count_total  
1        2017-04-20       a              2            4
1        2017-04-20       x              2            4
2        2017-04-20       a              2            3
2        2017-04-19       b              4            5    

Id=1 और date=2017-04-20 के लिए दो पंक्तियों पर ध्यान दें और दोनों में count_total=4
मुझे यकीन नहीं है कि आप यही चाहते हैं - भले ही आपने अपने प्रश्न में इस परिदृश्य पर विचार भी न किया हो

वैसे भी, मुझे लगता है कि अधिक सामान्य मामले का समर्थन करने के लिए ऊपर की तरह आउटपुट की आपकी अपेक्षा नीचे की तरह होनी चाहिए

Row id  date        x.columnX   x.countX    count_total  
1   1   2017-04-20  x           2           4    
                    a           2        
2   2   2017-04-20  a           2           3    
3   2   2017-04-19  b           4           5    

जहां एक्स दोहराया गया क्षेत्र है और प्रत्येक मान संबंधित कॉलम एक्स को इसकी गिनती के साथ दर्शाता है

नीचे दी गई क्वेरी ठीक यही करती है

#standardSQL
SELECT id, date,
  ARRAY(SELECT x FROM UNNEST(x) AS x WHERE countX > 1) AS x,
  count_total
FROM (
  SELECT id, date, SUM(countX) AS count_total,
    ARRAY_AGG(STRUCT<columnX STRING, countX INT64>(columnX, countX) ORDER BY countX DESC) AS X    
  FROM (
    SELECT id, date, 
      columnX, COUNT(1) countX
    FROM  `yourTable`
    GROUP BY id, date, columnX
  )
  GROUP BY id, date
  HAVING count_total > 1
)

आप इसे अपने प्रश्न से डमी डेटा के साथ खेल/परीक्षण कर सकते हैं

#standardSQL
WITH `yourTable` AS(
  SELECT 1 AS id, '2017-04-20' AS date, 'a' AS columnX UNION ALL
  SELECT 1, '2017-04-20', 'a' UNION ALL
  SELECT 1, '2017-04-20', 'x' UNION ALL
  SELECT 1, '2017-04-20', 'x' UNION ALL
  SELECT 1, '2017-04-18', 'b' UNION ALL
  SELECT 1, '2017-04-17', 'c' UNION ALL
  SELECT 2, '2017-04-20', 'a' UNION ALL
  SELECT 2, '2017-04-20', 'a' UNION ALL
  SELECT 2, '2017-04-20', 'c' UNION ALL
  SELECT 2, '2017-04-19', 'b' UNION ALL
  SELECT 2, '2017-04-19', 'b' UNION ALL
  SELECT 2, '2017-04-19', 'b' UNION ALL
  SELECT 2, '2017-04-19', 'b' UNION ALL
  SELECT 2, '2017-04-19', 'c'  
)
SELECT id, date,
  ARRAY(SELECT x FROM UNNEST(x) AS x WHERE countX > 1) AS x,
  count_total
FROM (
  SELECT id, date, SUM(countX) AS count_total,
    ARRAY_AGG(STRUCT<columnX STRING, countX INT64>(columnX, countX) ORDER BY countX DESC) AS X    
  FROM (
    SELECT id, date, 
      columnX, COUNT(1) countX
    FROM  `yourTable`
    GROUP BY id, date, columnX
  )
  GROUP BY id, date
  HAVING count_total > 1
)
1
Mikhail Berlyant 22 अगस्त 2017, 00:27