دیتابیس و SQLSQL از صفردرس ۵ از ۱۰

گروه‌بندی و گزارش

از داده گزارش بساز: تابع‌های تجمعی count و sum و avg و min و max، فرق count(*) و count(ستون) با NULL، گروه‌بندی با GROUP BY، فیلتر گروه‌ها با HAVING و فرقش با WHERE، count با FILTER، دسته‌بندی دلخواه با CASE، گروه‌بندی بر اساس ماه، string_agg و زیرپرس‌وجوی ساده.

حدود ۲۱ دقیقه مطالعه
۹ مثال اجراشده
۳ تمرین با جواب
مقدماتی تا متوسط
به‌روزرسانی: ۲۹ مهر ۱۴۰۵

توی این درس یاد می‌گیری به‌جای دیدن تک‌تک سطرها، از آن‌ها خلاصه بسازی: «چند محصول داریم؟»، «ارزش کل انبار چقدر است؟»، «میانگین قیمت هر دسته چند است؟». با تابع‌های تجمعی (count، sum، avg، min، max) کار می‌کنی، با GROUP BY سطرها را دسته‌دسته می‌کنی و برای هر دسته یک سطر خلاصه می‌گیری، و با HAVING دسته‌ها را فیلتر می‌کنی. این همان چیزی است که پشت هر داشبورد و گزارش مدیریتی هست.

مسئله: مدیر جدول نمی‌خواهد، عدد می‌خواهد

درس قبل یاد گرفتی سطرها را فیلتر کنی. ولی سؤال‌های واقعی معمولاً درباره‌ی مجموع‌اند: مدیر فروش فهرست ده هزار محصول را نمی‌خواهد؛ می‌خواهد بداند هر دسته چند کالا دارد و ارزش موجودی‌اش چقدر است. خروجی گرفتن و جمع زدن در اکسل هم کند است و هم هر بار باید تکرار شود.

تشبیه: کیسه‌های میوه و برچسب

GROUP BY مثل این است که میوه‌های یک سینی را بر اساس نوع در کیسه‌های جدا بریزی: سیب‌ها یک کیسه، پرتقال‌ها یک کیسه. بعد روی هر کیسه یک برچسب می‌زنی: «۱۲ عدد، ۳ کیلو، گران‌ترین ۲۰ هزار». تابع‌های تجمعی همان چیزهایی‌اند که روی برچسب می‌نویسی. WHERE قبل از کیسه کردن میوه‌های خراب را دور می‌ریزد؛ HAVING بعد از برچسب زدن، کیسه‌هایی را کنار می‌گذارد که برچسبشان شرط را ندارد (مثلاً «کیسه‌های سبک‌تر از ۲ کیلو»).

WHERE سطرها را قبل از گروه‌بندی فیلتر می‌کند و HAVING گروه‌ها را بعد از حساب شدن خلاصه‌ها.

مثال‌های عملی

جدول را تازه کن تا عددهایت با درس یکی باشد:

psql shop -f ~/products.sql
psql shop

مثال ۱: تابع‌های تجمعی روی کل جدول

SELECT count(*) FROM products;
SELECT min(price), max(price), sum(stock), avg(price) FROM products;
SELECT round(avg(price)) AS avg_price FROM products;

 count
-------
    12
(1 row)

  min  |   max   | sum |         avg
-------+---------+-----+----------------------
 95000 | 3200000 | 239 | 1243750.000000000000
(1 row)

 avg_price
-----------
   1243750
(1 row)

تابع تجمعی چند سطر را می‌گیرد و یک مقدار برمی‌گرداند. count(*) تعداد سطرها، sum جمع، avg میانگین، min و max کمینه و بیشینه. avg اعشار زیادی می‌دهد؛ round گردش می‌کند (round(x, 1) یعنی یک رقم اعشار).

مثال ۲: `count(*)` و `count(ستون)` فرق دارند

SELECT count(*) AS total, count(discount) AS with_discount FROM products;
SELECT count(DISTINCT category) FROM products;

 total | with_discount
-------+---------------
    12 |             5
(1 row)

 count
-------
     4
(1 row)

count(*) همه‌ی سطرها را می‌شمارد؛ count(discount) فقط سطرهایی را که discountشان NULL نیست. یعنی ۵ محصول تخفیف دارند. count(DISTINCT category) تعداد مقدارهای متفاوت است: ۴ دسته. همین قانون برای بقیه هم هست: sum، avg، min و max مقدارهای NULL را نادیده می‌گیرند:

SELECT avg(discount), avg(coalesce(discount, 0)) FROM products;

         avg         |        avg
---------------------+--------------------
 12.0000000000000000 | 5.0000000000000000
(1 row)

میانگین تخفیفِ «محصول‌هایی که تخفیف دارند» ۱۲٪ است؛ میانگین روی «همه‌ی محصول‌ها» (بی‌تخفیف = صفر) ۵٪. هر دو درست‌اند، ولی جواب دو سؤال متفاوت‌اند. قبل از گزارش دادن، مطمئن شو کدام را می‌خواهی.

مثال ۳: `GROUP BY`، یک سطر برای هر دسته

SELECT category, count(*) AS items, sum(stock) AS units, round(avg(price)) AS avg_price
FROM products
GROUP BY category
ORDER BY units DESC;

  category   | items | units | avg_price
-------------+-------+-------+-----------
 شبکه        |     3 |   123 |   1381667
 ذخیره‌سازی   |     3 |    61 |   2080000
 لوازم جانبی |     3 |    38 |   1196667
 کتاب        |     3 |    17 |    316667
(4 rows)

GROUP BY category سطرها را بر اساس دسته جدا می‌کند و تابع‌ها برای هر گروه جداگانه حساب می‌شوند. ORDER BY می‌تواند از اسم مستعار (units) استفاده کند، چون بعد از SELECT اجرا می‌شود.

یک ستون محاسبه‌ای هم می‌شود جمع زد. ارزش ریالی موجودی هر دسته:

SELECT category, sum(price * stock) AS stock_value
FROM products
GROUP BY category
ORDER BY stock_value DESC;

  category   | stock_value
-------------+-------------
 ذخیره‌سازی   |    46550000
 لوازم جانبی |    28620000
 شبکه        |    18600000
 کتاب        |     5590000
(4 rows)

price * stock برای هر سطر حساب می‌شود و بعد sum آن‌ها را در هر گروه جمع می‌زند. شبکه بیشترین تعداد را داشت، ولی ذخیره‌سازی بیشترین ارزش را.

مثال ۴: قانون طلایی `GROUP BY`

SELECT name, count(*) FROM products;

ERROR:  column "products.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT name, count(*) FROM products;
               ^

count(*) همه را در یک سطر خلاصه می‌کند، ولی ۱۲ اسم مختلف هست؛ دیتابیس کدام را کنار عدد ۱۲ بگذارد؟ قانون: در کوئری‌ای که تابع تجمعی دارد، هر ستون معمولی در SELECT باید در GROUP BY هم باشد. یا ستون را به GROUP BY اضافه کن، یا داخل یک تابع تجمعی بگذارش.

مثال ۵: `WHERE` و `HAVING`

SELECT category, count(*) AS in_stock
FROM products
WHERE stock > 0
GROUP BY category
ORDER BY category;

  category   | in_stock
-------------+----------
 ذخیره‌سازی   |        3
 شبکه        |        2
 لوازم جانبی |        2
 کتاب        |        2
(4 rows)

WHERE stock > 0 قبل از گروه‌بندی سطرهای تمام‌شده را حذف کرد، پس هر گروه فقط کالاهای موجود را شمرد. حالا فقط دسته‌هایی که جمع موجودی‌شان حداقل ۵۰ است:

SELECT category, sum(stock) AS units
FROM products
GROUP BY category
HAVING sum(stock) >= 50
ORDER BY units DESC;

 category  | units
-----------+-------
 شبکه      |   123
 ذخیره‌سازی |    61
(2 rows)

شرط روی نتیجه‌ی تابع تجمعی است، پس باید در HAVING باشد. اگر همین را در WHERE بنویسی:

SELECT count(*) FROM products WHERE price > avg(price);

ERROR:  aggregate functions are not allowed in WHERE
LINE 1: SELECT count(*) FROM products WHERE price > avg(price);
                                                    ^

وقتی WHERE اجرا می‌شود، هنوز هیچ میانگینی حساب نشده. برای «محصول‌های گران‌تر از میانگین» اول میانگین را با یک زیرپرس‌وجو (کوئری داخل پرانتز) حساب کن:

SELECT name, price FROM products
WHERE price > (SELECT avg(price) FROM products)
ORDER BY price DESC;

      name      |  price
----------------+---------
 هارد اکسترنال  | 3200000
 SSD 512 گیگ    | 2750000
 روتر وای‌فای    | 2400000
 کیبورد مکانیکی | 1890000
 سوئیچ 8 پورت   | 1650000
 هدفون          | 1250000
(6 rows)

⚡ بررسی سریع

می‌خواهی دسته‌هایی را ببینی که میانگین قیمتشان بالای یک میلیون است. شرط کجا می‌رود؟

جواب درست: HAVING avg(price) > 1000000

شرط روی نتیجه‌ی تابع تجمعی هر گروه است، پس HAVING.

مثال ۶: چند شمارش در یک کوئری با `FILTER`

SELECT category,
       count(*) FILTER (WHERE stock = 0) AS out_of_stock,
       count(*) AS items
FROM products
GROUP BY category
ORDER BY category;

  category   | out_of_stock | items
-------------+--------------+-------
 ذخیره‌سازی   |            0 |     3
 شبکه        |            1 |     3
 لوازم جانبی |            1 |     3
 کتاب        |            1 |     3
(4 rows)

FILTER (WHERE ...) (مخصوص PostgreSQL) یک تابع تجمعی را فقط روی بخشی از سطرهای گروه اجرا می‌کند. همان گزارش دوشنبه‌ی سناریو، در یک کوئری.

مثال ۷: گروه‌های دلخواه با `CASE`

گاهی گروه‌ها در داده نیستند و باید بسازی‌شان، مثلاً «بازه‌ی قیمت». CASE مثل if است و برای هر سطر یک مقدار برمی‌گرداند:

SELECT CASE
           WHEN price < 500000 THEN 'ارزان'
           WHEN price < 2000000 THEN 'متوسط'
           ELSE 'گران'
       END AS band,
       count(*) AS items, min(price), max(price)
FROM products
GROUP BY band
ORDER BY min(price);

 band  | items |   min   |   max
-------+-------+---------+---------
 ارزان |     6 |   95000 |  450000
 متوسط |     3 | 1250000 | 1890000
 گران  |     3 | 2400000 | 3200000
(3 rows)

CASE شرط‌ها را به ترتیب امتحان می‌کند و اولین WHEN درست برنده است (برای همین price < 2000000 لازم نیست >= 500000 را هم بگوید). ORDER BY min(price) گروه‌ها را به ترتیب منطقی چید، نه الفبایی.

مثال ۸: گروه‌بندی بر اساس ماه و چسباندن اسم‌ها

SELECT to_char(added_on, 'YYYY-MM') AS month, count(*)
FROM products
GROUP BY month
ORDER BY month;

  month  | count
---------+-------
 2026-01 |     2
 2026-02 |     1
 2026-03 |     1
 2026-04 |     1
 2026-05 |     1
 2026-06 |     1
 2026-07 |     1
 2026-08 |     2
 2026-09 |     1
 2026-10 |     1
(10 rows)

to_char(تاریخ, 'YYYY-MM') تاریخ را به متن «سال-ماه» تبدیل می‌کند، پس همه‌ی روزهای یک ماه در یک گروه می‌افتند. (برای گزارش «فروش ماهانه» دقیقاً همین را می‌خواهی.) و string_agg متن‌های یک گروه را به هم می‌چسباند:

SELECT category, string_agg(name, '، ' ORDER BY name) AS names
FROM products
GROUP BY category
ORDER BY category;

  category   |                 names
-------------+----------------------------------------
 ذخیره‌سازی   | SSD 512 گیگ، فلش 64 گیگ، هارد اکسترنال
 شبکه        | روتر وای‌فای، سوئیچ 8 پورت، کابل شبکه
 لوازم جانبی | ماوس بی‌سیم، هدفون، کیبورد مکانیکی
 کتاب        | کتاب داکر، کتاب لینوکس، کتاب پایتون
(4 rows)

(ترتیب «کتاب» بعد از «لوازم» و «کیبورد» بعد از «هدفون» عجیب است؛ دلیلش در درس قبل گفته شد: مرتب‌سازی پیش‌فرض بر اساس کد یونیکد حرف‌هاست و «ک» فارسی کد بزرگ‌تری دارد.)

مثال ۹: تجمعی روی هیچ سطر

SELECT count(*) FROM products WHERE category = 'ناموجود';
SELECT sum(stock) FROM products WHERE category = 'ناموجود';
SELECT coalesce(sum(stock), 0) FROM products WHERE category = 'ناموجود';

 count
-------
     0
(1 row)

 sum
-----

(1 row)

 coalesce
----------
        0
(1 row)

count روی هیچ سطر صفر می‌دهد، ولی sum (و avg، min، max) NULL. اگر برنامه‌ات انتظار عدد دارد، coalesce(..., 0) بگذار.

پشت پرده: تجمع چطور حساب می‌شود

PostgreSQL برای GROUP BY معمولاً یکی از دو راه را انتخاب می‌کند: HashAggregate (برای هر گروه یک خانه در یک جدول درهم‌سازی در حافظه می‌سازد و با رد شدن از هر سطر، شمارنده و جمع آن خانه را به‌روز می‌کند) یا GroupAggregate (سطرها را بر اساس ستون گروه مرتب می‌کند و گروه‌های پشت‌سرهم را خلاصه می‌کند). در هر دو حالت، دیتابیس فقط یک بار از روی سطرها رد می‌شود و برای هر گروه فقط چند عدد (شمارنده، جمع) نگه می‌دارد، نه همه‌ی سطرها. برای همین avg روی میلیون‌ها سطر حافظه‌ی زیادی نمی‌خورد: میانگین همان «جمع تقسیم بر تعداد» است.

می‌توانی نقشه را ببینی (درس ایندکس مفصل‌تر):

EXPLAIN SELECT category, count(*) FROM products GROUP BY category;

                            QUERY PLAN
------------------------------------------------------------------
 HashAggregate  (cost=20.80..22.80 rows=200 width=40)
   Group Key: category
   ->  Seq Scan on products  (cost=0.00..17.20 rows=720 width=32)
(3 rows)

از پایین به بالا بخوان: اول همه‌ی سطرها خوانده می‌شوند (Seq Scan)، بعد HashAggregate بر اساس category گروه می‌کند. عددهای rows فعلاً تخمین‌اند (جدول تازه ساخته شده و دیتابیس هنوز آماری از آن ندارد).

WHERE روی سطرها کار می‌کند و تابع تجمعی نمی‌شناسد؛ HAVING روی گروه‌ها کار می‌کند و شرطش معمولاً یک تابع تجمعی است.

جدول‌های مرجع

تابع	نتیجه	روی NULL
`count(*)`	تعداد سطرها	می‌شمارد
`count(col)`	تعداد مقدارهای غیر NULL	نادیده
`count(DISTINCT col)`	تعداد مقدارهای متفاوت	نادیده
`sum(col)` / `avg(col)`	جمع / میانگین	نادیده؛ روی هیچ سطر: NULL
`min(col)` / `max(col)`	کمینه / بیشینه	نادیده
`string_agg(col, ', ')`	چسباندن متن‌ها	نادیده

بخش	کار	ترتیب اجرا
`WHERE`	فیلتر سطرها	۲
`GROUP BY`	دسته‌بندی	۳
`HAVING`	فیلتر گروه‌ها	۴
`SELECT`	خلاصه‌ها و اسم مستعار	۵
`ORDER BY` / `LIMIT`	مرتب و محدود	۶

اشتباهات رایج

۱) ستون اضافه بدون `GROUP BY`

must appear in the GROUP BY clause (مثال ۴). راه‌حل: هر ستون غیرتجمعیِ SELECT را به GROUP BY اضافه کن.

۲) تابع تجمعی در `WHERE`

aggregate functions are not allowed in WHERE. راه‌حل: شرط روی گروه در HAVING؛ مقایسه با میانگین کل با زیرپرس‌وجو.

۳) `count(ستون)` به‌جای `count(*)`

اگر ستون NULL داشته باشد، عدد کمتری می‌گیری و متوجه نمی‌شوی. راه‌حل: برای «تعداد سطر» همیشه count(*)؛ count(col) فقط وقتی که واقعاً «تعداد مقدارهای پر» را می‌خواهی.

۴) میانگین روی مقدارهای خالی

مثال ۲: ۱۲٪ یا ۵٪؟ راه‌حل: صریح تصمیم بگیر؛ اگر «نداشتن» یعنی صفر، avg(coalesce(col, 0)).

۵) تقسیم صحیح

sum(stock) / count(*) روی ستون‌های INTEGER تقسیم صحیح است (239 / 12 می‌شود 19، نه 19.9). راه‌حل: از avg استفاده کن یا یکی را اعشاری کن: sum(stock)::numeric / count(*) (::numeric یعنی تبدیل نوع).

تمرین

تمرینآسان

ارزش ریالی کل موجودی فروشگاه (جمع قیمت ضربدر موجودی همه‌ی محصول‌ها) و تعداد کل کالاها را در یک کوئری حساب کن.

دیدن جواب

SELECT sum(price * stock) AS total_value, sum(stock) AS units FROM products;

 total_value | units
-------------+-------
    99360000 |   239
(1 row)

بدون GROUP BY، کل جدول یک گروه است و یک سطر خلاصه می‌گیری.

تمرینمتوسط

برای هر دسته، تعداد محصول‌هایی که تخفیف دارند و میانگین درصد تخفیفشان (با یک رقم اعشار) را نشان بده. بعد فقط دسته‌هایی را نگه دار که میانگین قیمت‌شان بالای یک میلیون تومان است (کوئری جدا).

دیدن جواب

SELECT category, count(discount) AS discounted, round(avg(discount), 1) AS avg_discount
FROM products GROUP BY category ORDER BY category;

SELECT category, round(avg(price)) AS avg_price
FROM products GROUP BY category
HAVING avg(price) > 1000000
ORDER BY avg_price DESC;

  category   | discounted | avg_discount
-------------+------------+--------------
 ذخیره‌سازی   |          1 |         10.0
 شبکه        |          1 |         20.0
 لوازم جانبی |          2 |         10.0
 کتاب        |          1 |         10.0
(4 rows)

  category   | avg_price
-------------+-----------
 ذخیره‌سازی   |   2080000
 شبکه        |   1381667
 لوازم جانبی |   1196667
(3 rows)

count(discount) فقط سطرهای دارای تخفیف را شمرد و avg(discount) میانگین همان‌ها را. کتاب‌ها با میانگین ۳۱۶ هزار تومان با HAVING حذف شدند.

تمرینسخت

یک گزارش «سلامت انبار» بنویس که برای هر دسته این ستون‌ها را داشته باشد: تعداد کل، تعداد تمام‌شده، تعداد کم‌موجودی (بین ۱ تا ۵)، و ارزش موجودی به میلیون تومان با یک رقم اعشار. فقط دسته‌هایی را نشان بده که حداقل یک کالای تمام‌شده یا کم‌موجودی دارند، و به ترتیب ارزش (زیاد به کم) مرتب کن.

دیدن جواب

SELECT category,
       count(*) AS items,
       count(*) FILTER (WHERE stock = 0) AS out_of_stock,
       count(*) FILTER (WHERE stock BETWEEN 1 AND 5) AS low,
       round(sum(price * stock) / 1000000.0, 1) AS value_m
FROM products
GROUP BY category
HAVING count(*) FILTER (WHERE stock <= 5) > 0
ORDER BY value_m DESC;

  category   | items | out_of_stock | low | value_m
-------------+-------+--------------+-----+---------
 ذخیره‌سازی   |     3 |            0 |   1 |    46.6
 لوازم جانبی |     3 |            1 |   0 |    28.6
 شبکه        |     3 |            1 |   1 |    18.6
 کتاب        |     3 |            1 |   1 |     5.6
(4 rows)

تقسیم بر 1000000.0 (با نقطه) نتیجه را اعشاری می‌کند تا round(..., 1) معنی داشته باشد. HAVING هم می‌تواند FILTER داشته باشد. اینجا همه‌ی دسته‌ها شرط را دارند؛ با psql shop -f ~/products.sql و یک UPDATE روی موجودی هارد اکسترنال (مثلاً stock = 20) دوباره اجرا کن و ببین ذخیره‌سازی حذف می‌شود.

آزمونک

در جدولی با ۱۲ سطر که ۷ سطرش discount خالی دارد، count(discount) چند است؟
GROUP BY category چه می‌کند؟
فرق WHERE و HAVING؟
sum(stock) روی صفر سطر چه برمی‌گرداند؟
خطای «must appear in the GROUP BY clause» یعنی…
count(*) FILTER (WHERE stock = 0) چه می‌شمارد؟

جمع‌بندی

تابع تجمعی چند سطر را به یک مقدار تبدیل می‌کند: count، sum، avg، min، max، string_agg.
count(*) همه‌ی سطرها؛ count(col) و بقیه‌ی تابع‌ها NULL را نادیده می‌گیرند.
GROUP BY برای هر گروه یک سطر خلاصه می‌سازد؛ هر ستون غیرتجمعیِ SELECT باید در آن باشد.
WHERE قبل از گروه‌بندی سطرها را فیلتر می‌کند، HAVING بعد از آن گروه‌ها را.
FILTER برای چند شمارش در یک کوئری، CASE برای ساختن گروه دلخواه، to_char برای گروه‌بندی زمانی.
مقایسه با میانگین کل: زیرپرس‌وجو (SELECT avg(...) FROM ...).

برگه‌ی تقلب این درس

دستور	کاری که می‌کند
`SELECT count(*) FROM t;`	تعداد سطرها
`SELECT sum(a), avg(a), min(a), max(a) FROM t;`	خلاصه‌ها
`SELECT c, count(*) FROM t GROUP BY c;`	یک سطر برای هر گروه
`... GROUP BY c HAVING count(*) > 2`	فیلتر گروه‌ها
`count(*) FILTER (WHERE x = 0)`	شمارش شرطی
`CASE WHEN ... THEN ... ELSE ... END`	مقدار شرطی
`to_char(d, 'YYYY-MM')`	گروه‌بندی ماهانه
`coalesce(sum(a), 0)`	صفر به‌جای NULL

مسئله: مدیر جدول نمی‌خواهد، عدد می‌خواهد

تشبیه: کیسه‌های میوه و برچسب

مثال‌های عملی

مثال ۱: تابع‌های تجمعی روی کل جدول

مثال ۲: count(*) و count(ستون) فرق دارند

مثال ۳: GROUP BY، یک سطر برای هر دسته

مثال ۴: قانون طلایی GROUP BY

مثال ۵: WHERE و HAVING

مثال ۶: چند شمارش در یک کوئری با FILTER

مثال ۷: گروه‌های دلخواه با CASE

مثال ۸: گروه‌بندی بر اساس ماه و چسباندن اسم‌ها

مثال ۹: تجمعی روی هیچ سطر

پشت پرده: تجمع چطور حساب می‌شود

جدول‌های مرجع

اشتباهات رایج

۱) ستون اضافه بدون GROUP BY

۲) تابع تجمعی در WHERE

۳) count(ستون) به‌جای count(*)

۴) میانگین روی مقدارهای خالی

۵) تقسیم صحیح

تمرین

آزمونک

جمع‌بندی

مثال ۲: `count(*)` و `count(ستون)` فرق دارند

مثال ۳: `GROUP BY`، یک سطر برای هر دسته

مثال ۴: قانون طلایی `GROUP BY`

مثال ۵: `WHERE` و `HAVING`

مثال ۶: چند شمارش در یک کوئری با `FILTER`

مثال ۷: گروه‌های دلخواه با `CASE`

۱) ستون اضافه بدون `GROUP BY`

۲) تابع تجمعی در `WHERE`

۳) `count(ستون)` به‌جای `count(*)`