شرح - sql count group by




حدد الصف الأول في كل مجموعة GROUP BY؟ (7)

كما يقترح العنوان ، أرغب في تحديد الصف الأول من كل مجموعة من الصفوف مجمعة مع GROUP BY .

على وجه التحديد ، إذا حصلت على جدول purchases يبدو كالتالي:

SELECT * FROM purchases;

الإخراج الخاص بي:

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

أود الاستعلام عن id أكبر عملية شراء ( total ) التي يقوم بها كل customer . شيء من هذا القبيل:

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.4 و 9.5 مع جدول واقعية في منتصف الطريق من 200k الصفوف في purchases و 10k customer_id متميزة ( متوسط ​​20 صف لكل عميل ).

بالنسبة لـ Postgres 9.5 ، أجريتُ اختبارًا ثانيًا مع 86446 عميلًا متميزًا. انظر أدناه ( متوسط ​​3.2 صف لكل عميل ).

اقامة

الجدول الرئيسي

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 (PK قيد مضاف أدناه) وعدد صحيح من client_id لأن هذا هو الإعداد أكثر نموذجية. تمت إضافة some_column للتعويض عن مزيد من الأعمدة بشكل نموذجي.

بيانات وهمية ، PK ، مؤشر - الجدول النموذجي أيضا لديه بعض الصفات الميتة:

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 لتوليد customer_id للحصول على عدد قليل فقط من الصفوف لكل customer_id .

أحجام الكائنات الخاصة 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() في CTE ، ( راجع إجابة أخرى )

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. 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. rCTE مع LATERAL الفرعي LATERAL ( راجع هنا )

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. جدول customer مع LATERAL ( انظر هنا )

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 مع 200 ألف صف و 20 لكل customer_id

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

ب نفسه مع Postgres 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  

ج ـ نفس درجة B. ، لكن مع 2.3 صفوفًا لكل customer_id

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

أجريت ثلاثة اختبارات مع برنامج PostgreSQL 9.1 على جدول حياة حقيقي يضم 65579 صفًا ومؤشرات btree أحادية العمود على كل من الأعمدة الثلاثة المعنية ، واستغرق وقت التنفيذ الأفضل من 5 أشواط.
مقارنة الاستعلام الأول لـOMGPonies ( A ) مع حل DISTINCT ON المذكور أعلاه ( B ):

  1. حدد الجدول بأكمله ، ينتج عنه 5958 صفًا في هذه الحالة.

    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

على Oracle 9.2+ (وليس 8i + كما هو مذكور أصلاً) ، SQL Server 2005+ ، PostgreSQL 8.4 أو أحدث ، DB2 ، Firebird 3.0+ ، Teradata ، Sybase ، Vertica:

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

كيف يعمل هذا! (لقد كنت هناك)

نريد أن نتأكد من أن لدينا فقط أعلى إجمالي لكل عملية شراء.

بعض الاشياء النظرية (تخطي هذا الجزء إذا كنت تريد فقط فهم الاستعلام)

دع شركة Total تكون دالة T (اسم العميل ، id) حيث تقوم بإرجاع قيمة معينة بالاسم والمعرّف لإثبات أن الإجمالي المعطى (T (الزبون ، id)) هو الأعلى يجب أن نثبت أننا نريد أن نثبت

  • ∀x T (الزبون ، id)> T (العميل ، x) (هذا الإجمالي أعلى من المجموع الكلي للعميل)

أو

  • ¬∃x T (الزبون ، المُعرف) <T (العميل ، x) (لا يوجد إجمالي أعلى لهذا العميل)

النهج الأول سيحتاجنا للحصول على جميع السجلات لهذا الاسم التي لا أحبها حقًا.

سوف يحتاج الثاني إلى طريقة ذكية ليقول أنه لا يمكن أن يكون هناك سجل أعلى من هذا.

العودة إلى SQL

إذا غادرنا ، فسننضم إلى الجدول الموجود على الاسم والإجمالي أقل من الجدول المرتبط:

      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

وهذا هو الجواب الذي نحتاجه.


الحل ليس فعالاً للغاية كما أشار إليه اروين ، بسبب وجود طلبات فرعية

select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;

في 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 للمعيار (حيث يتم تعريف DISTINCT فقط في قائمة SELECT بالكامل).

  • أدرج أي عدد من التعبيرات في جملة DISTINCT ON ، فإن قيمة الصف المدمجة تُعرّف التكرارات. الدليل:

    من الواضح أن هناك صفين مختلفين إذا اختلفا في قيمة عمود واحد على الأقل. تعتبر القيم الخالية متساوية في هذه المقارنة.

    التركيز الجريء

  • يمكن دمج DISTINCT ON مع ORDER BY . يجب أن تطابق التعبيرات الرائدة DISTINCT ON الموجودة بنفس الترتيب. يمكنك إضافة تعبيرات إضافية إلى ORDER BY لاختيار صف معين من كل مجموعة من الزملاء. لقد أضفت id كعنصر آخر لكسر الروابط:

    "اختر الصف بأصغر id من كل مجموعة يتشارك في أعلى total ."

    إذا كان 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 في الاستعلام ، فاستخدم نفس الشيء في الفهرس بحيث يعرف Postgres تطابقات ترتيب الفرز.

فعالية / تحسين الأداء

يجب عليك تقييم التكلفة والفائدة قبل إنشاء فهرس مخصص لكل استعلام. تعتمد إمكانات المؤشر أعلاه بشكل كبير على توزيع البيانات .

يتم استخدام الفهرس لأنه يسلم بيانات تم فرزها مسبقًا ، وفي Postgres 9.2 أو أحدث ، يمكن أن يستفيد الاستعلام أيضًا من فهرس يتم فحصه فقط إذا كان الفهرس أصغر من الجدول الأساسي. يجب فحص الفهرس بأكمله.

المعيار

كان لدي معيار بسيط هنا لـ Postgres 9.1 ، والذي كان قد عفا عليه الزمن بحلول عام 2016. لذا قمت بتشغيل واحد جديد مع إعداد أفضل وقابل للتكرار لـ Postgres 9.4 و 9.5 وإضافة النتائج التفصيلية في إجابة أخرى .


في Postgres ، يمكنك استخدام array_agg مثل هذا:

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

سيعطيك هذا رقم تعريف أكبر عملية شراء لكل عميل.

بعض الأشياء التي يجب ملاحظتها:

  • array_agg هي دالة مجمعة ، لذا فهي تعمل مع GROUP BY .
  • يتيح لك array_agg تحديد طلب محدد لتحديد array_agg فقط ، حتى لا يقيد بنية الاستعلام بالكامل. هناك أيضا بناء جملة لكيفية فرز NULLs ، إذا كنت بحاجة إلى القيام بشيء مختلف عن الافتراضي.
  • بمجرد إنشاء الصفيف ، نأخذ العنصر الأول. (الصفائف Postgres هي 1-مفهرسة ، وليس 0-مفهرسة).
  • يمكنك استخدام array_agg بطريقة مشابهة لعمود الإخراج الثالث ، ولكن max(total) أبسط.
  • بخلاف DISTINCT ON ، يتيح لك استخدام array_agg الاحتفاظ بـ GROUP BY ، في حال كنت تريد ذلك لأسباب أخرى.

يمكنني استخدام هذه الطريقة (postgresql فقط): 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;

CAVEAT: تجاهل الصفوف NULL

تحرير 1 - استخدم ملحق postgres بدلاً من ذلك

الآن استخدم هذه الطريقة: http://pgxn.org/dist/first_last_agg/

للتثبيت على ubuntu 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'

انها امتداد postgres الذي يمنحك الوظائف الأولى والأخيرة. على ما يبدو أسرع من الطريقة المذكورة أعلاه.

تحرير 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