[pgsql-jp: 37819] Re: 階級ごとの最大値や最小値の取り出し方を教えてください

[渋谷泰宏]

渋谷です。

＃ちょっと長いですが・・・

■ 出力イメージを以下のものとします。

pt_grp   | ckの最大値 | ckの最小値 | ckの平均値 | データ数
-------------+------------+------------+------------+----------
-10以上0未満 | NULL   | NULL   | NULL   | 0
0以上10未満  | 4          | 2          | 3          | 2
10以上20未満 | 5          | 5          | 5          | 1

※ pt_grp 列はクエリではとりあえず左側の数値のみ出力する。

■ クエリを見やすくするため３つビューを作成するとします。

/* 1. pt 列を加工した pt_grp 列を持つ擬似テーブル */
CREATE OR REPLACE VIEW "usr_pt_grp" (
  id, usr_id, pt, pt_grp
) AS
SELECT id, usr_id, pt,
  ((CASE WHEN (pt>=0) THEN pt ELSE (pt-9) END) / 10 * 10)
FROM usr_pt;

# 加工方法を少し変更しています。

/* 2. usr_pt_grp と usr_ck の結合ビュー */
CREATE OR REPLACE VIEW "usr_pt_ck" (
  id, usr_id, pt, pt_grp, chkdate, ck
) AS
SELECT PG.id, PG.usr_id, PG.pt, PG.pt_grp, CK.chkdate, CK.ck
FROM usr_pt_grp AS PG
INNER JOIN usr_ck AS CK
  ON PG.usr_id = CK.usr_id;

/* 3. pt_grp の擬似マスタ テーブル */
CREATE OR REPLACE VIEW "pt_grp" (
  pt_grp
) AS
SELECT pt_grp
FROM usr_pt_grp
GROUP BY pt_grp;


> １．出力イメージの識別列を左に外部結合する。

基準となる最も左の IDENTITY 列が分からないのですが、
これを出力イメージの pt_grp 列だとすると、こういうことです。

SELECT
  PG.pt_grp,
  max(PC.ck) AS ck_max,
  min(PC.ck) AS ck_min,
  avg(PC.ck) AS ck_avg,
  count(PC.ck) AS ck_cnt
FROM pt_grp AS PG
LEFT OUTER JOIN (
  SELECT pt_grp, ck
  FROM usr_pt_ck
  WHERE chkdate='2006-12-17'
) AS PC ON PC.pt_grp = PG.pt_grp
GROUP BY PG.pt_grp
ORDER BY PG.pt_grp;


> ２．集計元のデータに列を加える。

こっちは IDENTITY を pt_grp 列にすると例の記述と少し変わりますが、
こんな感じです。

SELECT
  PC.pt_grp,
  max(PC.ck) AS ck_max,
  min(PC.ck) AS ck_min,
  avg(PC.ck) AS ck_avg,
  count(PC.ck) AS ck_cnt
FROM (
    SELECT pt_grp, ck
    FROM usr_pt_ck
    WHERE chkdate='2006-12-17'
      UNION ALL
    SELECT pt_grp, NULL
    FROM pt_grp
) AS PC
GROUP BY PC.pt_grp
ORDER BY PC.pt_grp;

両者とも NULL でなく 0 の方が都合が良ければ coalesce でも使う。
後者の良いところは結合が一つ減ることでしょうか。

-- 
渋谷 泰宏