sql - समूह के प्रत्येक ग्रुप में पहली पंक्ति का चयन करें?




sqlite postgresql (7)

बेंचमार्क

पोस्टग्रेस 9.4 और 9.5 के साथ सबसे दिलचस्प उम्मीदवारों की जांच में 200k पंक्तियों की आधा यथार्थवादी तालिका और 10k विशिष्ट ग्राहक_आईडी ( प्रति ग्राहक औसत 20 पंक्तियां )।

पोस्टग्रेस 9.5 के लिए मैंने प्रभावी रूप से 86446 विशिष्ट ग्राहकों के साथ एक दूसरा परीक्षण चलाया। नीचे देखें ( प्रति ग्राहक औसत 2.3 पंक्तियां )।

सेट अप

मुख्य तालिका

CREATE TABLE purchases (
  id          serial
, customer_id int  -- REFERENCES customer
, total       int  -- could be amount of money in Cent
, some_column text -- to make the row bigger, more realistic
);

मैं एक serial (नीचे जोड़ा गया पीके बाधा) और एक पूर्णांक ग्राहक_आईडी का उपयोग करता हूं क्योंकि यह एक और अधिक सामान्य सेटअप है। आम तौर पर अधिक कॉलम बनाने के लिए some_column भी जोड़ा।

डमी डेटा, पीके, इंडेक्स - एक ठेठ तालिका में कुछ मृत टुपल्स भी होते हैं:

INSERT INTO purchases (customer_id, total, some_column)    -- insert 200k rows
SELECT (random() * 10000)::int             AS customer_id  -- 10k customers
     , (random() * random() * 100000)::int AS total     
     , 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int)
FROM   generate_series(1,200000) g;

ALTER TABLE purchases ADD CONSTRAINT purchases_id_pkey PRIMARY KEY (id);

DELETE FROM purchases WHERE random() > 0.9; -- some dead rows

INSERT INTO purchases (customer_id, total, some_column)
SELECT (random() * 10000)::int             AS customer_id  -- 10k customers
     , (random() * random() * 100000)::int AS total     
     , 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int)
FROM   generate_series(1,20000) g;  -- add 20k to make it ~ 200k

CREATE INDEX purchases_3c_idx ON purchases (customer_id, total DESC, id);

VACUUM ANALYZE purchases;

customer तालिका - बेहतर क्वेरी के लिए

CREATE TABLE customer AS
SELECT customer_id, 'customer_' || customer_id AS customer
FROM   purchases
GROUP  BY 1
ORDER  BY 1;

ALTER TABLE customer ADD CONSTRAINT customer_customer_id_pkey PRIMARY KEY (customer_id);

VACUUM ANALYZE customer;

9.5 के लिए मेरे दूसरे टेस्ट में मैंने एक ही सेटअप का इस्तेमाल किया, लेकिन random() * 100000 लिए केवल कुछ पंक्तियों को प्राप्त करने के लिए random() * 100000 उत्पन्न करने के लिए random() * 100000 साथ।

टेबल purchases लिए ऑब्जेक्ट आकार

इस क्वेरी के साथ उत्पन्न किया।

               what                | bytes/ct | bytes_pretty | bytes_per_row
-----------------------------------+----------+--------------+---------------
 core_relation_size                | 20496384 | 20 MB        |           102
 visibility_map                    |        0 | 0 bytes      |             0
 free_space_map                    |    24576 | 24 kB        |             0
 table_size_incl_toast             | 20529152 | 20 MB        |           102
 indexes_size                      | 10977280 | 10 MB        |            54
 total_size_incl_toast_and_indexes | 31506432 | 30 MB        |           157
 live_rows_in_text_representation  | 13729802 | 13 MB        |            68
 ------------------------------    |          |              |
 row_count                         |   200045 |              |
 live_tuples                       |   200045 |              |
 dead_tuples                       |    19955 |              |

प्रश्नों

1. सीटीई में row_number() , ( अन्य उत्तर देखें )

WITH cte AS (
   SELECT id, customer_id, total
        , row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn
   FROM   purchases
   )
SELECT id, customer_id, total
FROM   cte
WHERE  rn = 1;

2. subquery में row_number() (मेरा अनुकूलन)

SELECT id, customer_id, total
FROM   (
   SELECT id, customer_id, total
        , row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn
   FROM   purchases
   ) sub
WHERE  rn = 1;

3. DISTINCT ON ( अन्य उत्तर देखें )

SELECT DISTINCT ON (customer_id)
       id, customer_id, total
FROM   purchases
ORDER  BY customer_id, total DESC, id;

4. लैटरल सबक्वायरी के साथ आरसीटीई ( यहां देखें )

WITH RECURSIVE cte AS (
   (  -- parentheses required
   SELECT id, customer_id, total
   FROM   purchases
   ORDER  BY customer_id, total DESC
   LIMIT  1
   )
   UNION ALL
   SELECT u.*
   FROM   cte c
   ,      LATERAL (
      SELECT id, customer_id, total
      FROM   purchases
      WHERE  customer_id > c.customer_id  -- lateral reference
      ORDER  BY customer_id, total DESC
      LIMIT  1
      ) u
   )
SELECT id, customer_id, total
FROM   cte
ORDER  BY customer_id;

5. LATERAL साथ customer तालिका ( यहां देखें )

SELECT l.*
FROM   customer c
,      LATERAL (
   SELECT id, customer_id, total
   FROM   purchases
   WHERE  customer_id = c.customer_id  -- lateral reference
   ORDER  BY total DESC
   LIMIT  1
   ) l;

6. array_agg() ORDER BY साथ ( अन्य उत्तर देखें )

SELECT (array_agg(id ORDER BY total DESC))[1] AS id
     , customer_id
     , max(total) AS total
FROM   purchases
GROUP  BY customer_id;

परिणाम

EXPLAIN ANALYZE (और सभी विकल्प बंद ) के साथ उपरोक्त प्रश्नों के लिए निष्पादन समय, 5 सर्वश्रेष्ठ में से सर्वश्रेष्ठ

सभी प्रश्नों ने purchases2_3c_idx (अन्य चरणों के बीच) पर एक इंडेक्स केवल स्कैन का उपयोग किया। उनमें से कुछ सिर्फ सूचकांक के छोटे आकार के लिए, दूसरों को अधिक प्रभावी ढंग से।

ए पोस्टग्रेस 9.4 200k पंक्तियों के साथ और ~ 20 प्रति ग्राहक_आईडी

1. 273.274 ms  
2. 194.572 ms  
3. 111.067 ms  
4.  92.922 ms  
5.  37.679 ms  -- winner
6. 189.495 ms

बी पोस्टग्रेस 9.5 के साथ ही

1. 288.006 ms
2. 223.032 ms  
3. 107.074 ms  
4.  78.032 ms  
5.  33.944 ms  -- winner
6. 211.540 ms  

सी बी के समान है, लेकिन प्रति ग्राहक_आईडी ~ 2.3 पंक्तियों के साथ

1. 381.573 ms
2. 311.976 ms
3. 124.074 ms  -- winner
4. 710.631 ms
5. 311.976 ms
6. 421.679 ms

2011 से मूल (पुराना) बेंचमार्क

मैंने पोस्टग्रेएसक्यूएल 9.1 के साथ तीन परीक्षणों में भाग लिया जिसमें 65579 पंक्तियों की एक वास्तविक जीवन तालिका और सिंगल-कॉलम बिट इंडेक्स शामिल थे, जिसमें शामिल तीन कॉलम में से प्रत्येक पर 5 रनों का सर्वश्रेष्ठ निष्पादन समय लिया गया।
उपरोक्त DISTINCT ON समाधान ( B ) DISTINCT ON @OMGPonies की पहली क्वेरी ( A ) की तुलना करना :

  1. इस मामले में पूरी तालिका का चयन करें, जिसके परिणामस्वरूप 5 9 58 पंक्तियां होंगी।

    A: 567.218 ms
    B: 386.673 ms
    
  2. स्थिति का उपयोग करें WHERE customer BETWEEN x AND y 1000 पंक्तियों के परिणामस्वरूप होता है।

    A: 249.136 ms
    B:  55.111 ms
    
  3. WHERE customer = x साथ एक ग्राहक का चयन करें।

    A:   0.143 ms
    B:   0.072 ms
    

दूसरे उत्तर में वर्णित सूचकांक के साथ वही परीक्षण दोहराया गया

CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);

1A: 277.953 ms  
1B: 193.547 ms

2A: 249.796 ms -- special index not used  
2B:  28.679 ms

3A:   0.120 ms  
3B:   0.048 ms

जैसा कि शीर्षक से पता चलता है, मैं GROUP BY साथ समूहीकृत पंक्तियों के प्रत्येक सेट की पहली पंक्ति का चयन करना चाहता हूं।

विशेष रूप से, अगर मेरे पास एक purchases तालिका है जो इस तरह दिखती है:

SELECT * FROM purchases;

मेरा आउटपुट:

id | customer | total
---+----------+------
 1 | Joe      | 5
 2 | Sally    | 3
 3 | Joe      | 2
 4 | Sally    | 1

मैं प्रत्येक customer द्वारा बनाई गई सबसे बड़ी खरीद ( total ) की id के लिए पूछना चाहता हूं। कुछ इस तरह:

SELECT FIRST(id), customer, FIRST(total)
FROM  purchases
GROUP BY customer
ORDER BY total DESC;

अपेक्षित उत्पादन:

FIRST(id) | customer | FIRST(total)
----------+----------+-------------
        1 | Joe      | 5
        2 | Sally    | 3

ओरेकल 9.2+ (मूल रूप से 8i + के रूप में नहीं) पर, SQL सर्वर 2005+, PostgreSQL 8.4+, डीबी 2, फ़ायरबर्ड 3.0+, टेराडाटा, साइबेस, वर्टिका:

WITH summary AS (
    SELECT p.id, 
           p.customer, 
           p.total, 
           ROW_NUMBER() OVER(PARTITION BY p.customer 
                                 ORDER BY p.total DESC) AS rk
      FROM PURCHASES p)
SELECT s.*
  FROM summary s
 WHERE s.rk = 1

किसी भी डेटाबेस द्वारा समर्थित:

लेकिन आपको संबंधों को तोड़ने के लिए तर्क जोड़ने की जरूरत है:

  SELECT MIN(x.id),  -- change to MAX if you want the highest
         x.customer, 
         x.total
    FROM PURCHASES x
    JOIN (SELECT p.customer,
                 MAX(total) AS max_total
            FROM PURCHASES p
        GROUP BY p.customer) y ON y.customer = x.customer
                              AND y.max_total = x.total
GROUP BY x.customer, x.total

पूछताछ:

SELECT purchases.*
FROM purchases
LEFT JOIN purchases as p 
ON 
  p.customer = purchases.customer 
  AND 
  purchases.total < p.total
WHERE p.total IS NULL

वह कैसे काम करता है! (मैं वहाँ गया था)

हम यह सुनिश्चित करना चाहते हैं कि प्रत्येक खरीद के लिए हमारे पास केवल उच्चतम कुल है।

कुछ सैद्धांतिक सामग्री (यदि आप केवल क्वेरी को समझना चाहते हैं तो इस भाग को छोड़ दें)

कुल मिलाकर एक कार्य टी (ग्राहक, आईडी) जहां यह नाम और आईडी दिया गया मान देता है यह साबित करने के लिए कि दिया गया कुल (टी (ग्राहक, आईडी)) उच्चतम है, हमें यह साबित करना होगा कि हम या तो साबित करना चाहते हैं

  • ∀x टी (ग्राहक, आईडी)> टी (ग्राहक, एक्स) (यह कुल उस ग्राहक के लिए अन्य सभी कुल से अधिक है)

या

  • ¬x टी (ग्राहक, आईडी) <टी (ग्राहक, एक्स) (उस ग्राहक के लिए कोई उच्च कुल मौजूद नहीं है)

पहले दृष्टिकोण के लिए हमें उस नाम के लिए सभी रिकॉर्ड प्राप्त करने की आवश्यकता होगी जो मुझे वास्तव में पसंद नहीं है।

दूसरे को यह कहने का एक स्मार्ट तरीका चाहिए कि इस से कोई रिकॉर्ड अधिक नहीं हो सकता है।

एसक्यूएल पर वापस

अगर हम नाम पर तालिका में शामिल हो जाते हैं और कुल मिलाकर तालिका से कम है:

      LEFT JOIN purchases as p 
      ON 
      p.customer = purchases.customer 
      AND 
      purchases.total < p.total

हम यह सुनिश्चित करते हैं कि सभी रिकॉर्ड्स जिनके पास एक ही उपयोगकर्ता के लिए उच्चतम कुल मिलाकर एक और रिकॉर्ड हो,

purchases.id, purchases.customer, purchases.total, p.id, p.customer, p.total
1           , Tom           , 200             , 2   , Tom   , 300
2           , Tom           , 300
3           , Bob           , 400             , 4   , Bob   , 500
4           , Bob           , 500
5           , Alice         , 600             , 6   , Alice   , 700
6           , Alice         , 700

इससे हमें प्रत्येक समूह के लिए उच्चतम कुल फ़िल्टर करने में मदद मिलेगी, जिसमें कोई समूह आवश्यक नहीं है:

WHERE p.total IS NULL

purchases.id, purchases.name, purchases.total, p.id, p.name, p.total
2           , Tom           , 300
4           , Bob           , 500
6           , Alice         , 700

और यही वह जवाब है जिसकी हमें आवश्यकता है।


पोस्टग्रेज़ में आप array_agg उपयोग इस तरह कर सकते हैं:

SELECT  customer,
        (array_agg(id ORDER BY total DESC))[1],
        max(total)
FROM purchases
GROUP BY customer

यह आपको प्रत्येक ग्राहक की सबसे बड़ी खरीद की id देगा।

कुछ बातें ध्यान दें:

  • array_agg एक समग्र कार्य है, इसलिए यह GROUP BY साथ काम करता है।
  • array_agg आपको केवल ऑर्डर करने के लिए ऑर्डर करने की अनुमति देता है, इसलिए यह पूरी क्वेरी की संरचना को बाधित नहीं करता है। अगर आपको डिफ़ॉल्ट से कुछ अलग करने की ज़रूरत है, तो आप एनयूएलएल को कैसे क्रमबद्ध करते हैं, इसके लिए सिंटैक्स भी है।
  • एक बार जब हम सरणी बनाते हैं, तो हम पहला तत्व लेते हैं। (पोस्टग्रेज़ सरणी 1-अनुक्रमित हैं, 0-अनुक्रमित नहीं)।
  • आप अपने तीसरे आउटपुट कॉलम के लिए उसी तरह array_agg का उपयोग कर सकते हैं, लेकिन max(total) सरल है।
  • DISTINCT ON विपरीत, array_agg का उपयोग करके आप अपने GROUP BY रख सकते हैं, यदि आप अन्य कारणों से चाहते हैं।

मैं इस तरह का उपयोग करता हूं (केवल पोस्टग्रेस्क्ल): https://wiki.postgresql.org/wiki/First/last_%28aggregate%29

-- Create a function that always returns the first non-NULL item
CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $1;
$$;

-- And then wrap an aggregate around it
CREATE AGGREGATE public.first (
        sfunc    = public.first_agg,
        basetype = anyelement,
        stype    = anyelement
);

-- Create a function that always returns the last non-NULL item
CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $2;
$$;

-- And then wrap an aggregate around it
CREATE AGGREGATE public.last (
        sfunc    = public.last_agg,
        basetype = anyelement,
        stype    = anyelement
);

फिर आपका उदाहरण लगभग काम करना चाहिए:

SELECT FIRST(id), customer, FIRST(total)
FROM  purchases
GROUP BY customer
ORDER BY FIRST(total) DESC;

चेतावनी: यह नल की पंक्तियों को अनदेखा करता है

संपादित करें 1 - इसके बजाय पोस्टग्रेस एक्सटेंशन का उपयोग करें

अब मैं इस तरह का उपयोग करता हूं: http://pgxn.org/dist/first_last_agg/

उबंटू 14.04 पर स्थापित करने के लिए:

apt-get install postgresql-server-dev-9.3 git build-essential -y
git clone git://github.com/wulczer/first_last_agg.git
cd first_last_app
make && sudo make install
psql -c 'create extension first_last_agg'

यह एक पोस्टग्रेस एक्सटेंशन है जो आपको पहले और अंतिम कार्यों देता है; उपर्युक्त तरीके से स्पष्ट रूप से तेज़।

2 संपादित करें - ऑर्डरिंग और फ़िल्टरिंग

यदि आप कुल फ़ंक्शंस (जैसे) का उपयोग करते हैं, तो आप परिणामों को ऑर्डर कर सकते हैं, बिना डेटा को पहले से ऑर्डर करने की आवश्यकता के बिना:

http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES

तो आदेश के साथ समकक्ष उदाहरण कुछ ऐसा होगा:

SELECT first(id order by id), customer, first(total order by id)
  FROM purchases
 GROUP BY customer
 ORDER BY first(total);

बेशक आप ऑर्डर कर सकते हैं और फिल्टर कर सकते हैं जैसा कि आप कुल मिलाकर फिट बैठते हैं; यह बहुत शक्तिशाली वाक्यविन्यास है।


यह आम greatest-n-per-group समस्या है, जो पहले से ही अच्छी तरह से परीक्षण और अत्यधिक अनुकूलित समाधान है । व्यक्तिगत रूप से मैं बिल करविन ( अन्य कई समाधानों के साथ मूल पोस्ट ) द्वारा बाएं शामिल समाधान को प्राथमिकता देता हूं।

ध्यान दें कि इस सामान्य समस्या के समाधान का गुच्छा आश्चर्यजनक रूप से सबसे आधिकारिक स्रोतों में से एक में पाया जा सकता है, MySQL मैनुअल ! सामान्य प्रश्नों के उदाहरण देखें :: पंक्तियों को समूह के अनुसार कुछ निश्चित कॉलम होल्डिंग


PostgreSQL में यह आमतौर पर सरल और तेज (नीचे अधिक प्रदर्शन अनुकूलन) है:

SELECT DISTINCT ON (customer)
       id, customer, total
FROM   purchases
ORDER  BY customer, total DESC, id;

या आउटपुट कॉलम की सामान्य संख्या के साथ छोटा (यदि स्पष्ट नहीं है):

SELECT DISTINCT ON (2)
       id, customer, total
FROM   purchases
ORDER  BY 2, 3 DESC, 1;

यदि total शून्य हो सकता है (किसी भी तरह से चोट नहीं पहुंचाएगा, लेकिन आप मौजूदा इंडेक्स से मेल खाना चाहते हैं):

...
ORDER  BY customer, total DESC NULLS LAST, id;

प्रमुख अंक

  • DISTINCT ON मानक का एक PostgreSQL एक्सटेंशन है (जहां पूरी SELECT सूची पर केवल DISTINCT परिभाषित किया गया है)।

  • DISTINCT ON क्लॉज में अभिव्यक्तियों की किसी भी संख्या की सूची बनाएं, संयुक्त पंक्ति मान डुप्लीकेट को परिभाषित करता है। नियम पुस्तिका:

    जाहिर है, यदि वे कम से कम एक कॉलम मान में भिन्न होते हैं तो दो पंक्तियों को अलग माना जाता है। इस तुलना में शून्य मानों को बराबर माना जाता है।

    बोल्ड जोर मेरा है।

  • DISTINCT ON को ORDER BY किया जा सकता है। प्रमुख अभिव्यक्तियों को उसी क्रम में अभिव्यक्तियों DISTINCT ON अग्रणी DISTINCT ON से मेल खाना पड़ेगा। आप सहकर्मियों के प्रत्येक समूह से एक विशेष पंक्ति चुनने के लिए ORDER BY अतिरिक्त अभिव्यक्ति जोड़ सकते हैं। मैंने संबंधों को तोड़ने के लिए id को अंतिम आइटम के रूप में जोड़ा:

    "उच्चतम total साझा करने वाले प्रत्येक समूह से सबसे छोटी id साथ पंक्ति चुनें।"

    यदि total शून्य हो सकता है, तो संभवतः आप सबसे बड़ी गैर-शून्य मान वाली पंक्ति चाहते हैं। प्रदर्शन की तरह NULLS LAST जोड़ें। विवरण:

  • SELECT सूची किसी भी तरह से DISTINCT ON या ORDER BY में अभिव्यक्तियों से बाधित नहीं है। (उपरोक्त साधारण मामले में आवश्यक नहीं है):

    • आपको DISTINCT ON या ORDER BY में किसी भी अभिव्यक्ति को शामिल करने की आवश्यकता नहीं है

    • आप SELECT सूची में कोई अन्य अभिव्यक्ति शामिल कर सकते हैं। यह सबक्वायरीज़ और कुल / विंडो फ़ंक्शंस के साथ अधिक जटिल प्रश्नों को बदलने के लिए महत्वपूर्ण है।

  • मैंने संस्करण 8.3 - 10 के साथ परीक्षण किया है लेकिन यह संस्करण संस्करण 7.1 के बाद से कम से कम रहा है, इसलिए मूल रूप से हमेशा।

सूची

उपर्युक्त क्वेरी के लिए एकदम सही अनुक्रमणिका एक बहु-कॉलम इंडेक्स होगी जो मेलिंग अनुक्रम में सभी तीन कॉलम फैलाएगी और मिलान क्रमबद्ध क्रम के साथ होगी:

CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);

असली दुनिया के अनुप्रयोगों के लिए बहुत विशिष्ट हो सकता है। लेकिन अगर पढ़ना प्रदर्शन महत्वपूर्ण है तो इसका इस्तेमाल करें। यदि आपके पास क्वेरी में DESC NULLS LAST है, तो इंडेक्स में इसका इस्तेमाल करें ताकि पोस्टग्रेस सॉर्ट ऑर्डर मैचों को जानता हो।

प्रभावशीलता / प्रदर्शन अनुकूलन

प्रत्येक क्वेरी के लिए एक अनुरूप सूचकांक बनाने से पहले आपको लागत और लाभ का वजन करना होगा। उपरोक्त सूचकांक की संभावना काफी हद तक डेटा वितरण पर निर्भर करती है।

इंडेक्स का उपयोग किया जाता है क्योंकि यह प्री-सॉर्ट किए गए डेटा को वितरित करता है, और पोस्टग्रेस 9.2 में या बाद में क्वेरी इंडेक्स से भी लाभ उठा सकती है, अगर इंडेक्स अंतर्निहित तालिका से छोटा है तो स्कैन करें । सूचकांक को पूरी तरह से स्कैन किया जाना चाहिए, यद्यपि।

बेंचमार्क

पोस्टग्रेस 9.1 के लिए मेरे पास एक साधारण बेंचमार्क था, जो 2016 तक पुराना था। इसलिए मैंने पोस्टग्रेस 9.4 और 9.5 के लिए एक बेहतर, पुनरुत्पादित सेटअप के साथ एक नया भाग लिया और विस्तृत परिणामों को एक और उत्तर में जोड़ा।





greatest-n-per-group