レコード抽出条件について

Question

XAMPPにあるMySQL（10.3.15-MariaDB）とPHP7.3.6でプログラムを自作しています。

下記の通り、材料と加工の集計を導き出し、集計を行うことを行いたいと思っています。

CREATE VIEW `flow_a120_sum_view`  AS
		WITH `maxq` AS (
			SELECT
				*,
				CONCAT ( `pnum_`, `bnum_` , `inum_` ) AS `_sid_`,
				MAX(`pnow_`) OVER ( PARTITION BY `pnum_`, `bnum_`,`inum_`, `pslct`, `enum_`) AS `mxpnw`
			FROM
				`flow_a120tbl`
		)
SELECT
	`_sid_`,
	 COUNT(`pmony` <> 0 or NULL) AS `_evq_`,
	 SUM( CASE `pslct` WHEN '材料' THEN `pmony` ELSE 0 END ) AS `smmtm`,
	 SUM( CASE `pslct` WHEN '加工' THEN `pmony` ELSE 0 END ) AS `smprm`
	
FROM
	`maxq`
WHERE
	`pnow_` = `mxpnw`
GROUP BY
	`pnum_`,
	`bnum_`,
	`inum_`
;

上記の方法だと「pnow_値が最大のとき」のレコードを抽出することができました。
これに付け加えて「gnum_値が最大のとき」も条件に加えるにはどうすればよいのでしょうか。

やってみたこと

• 「pnow_値が最大のとき」の構文「MAX(pnow_) OVER ( PARTITION　・・・（略）」を複製して「MAX(gnum_) OVER ( PARTITION　・・・（略）」を追記してみましたがうまくいきませんでした。

CREATE VIEW `flow_a120_sum_view`  AS
		WITH `maxq` AS (
			SELECT
				*,
				CONCAT ( `pnum_`, `bnum_` , `inum_` ) AS `_sid_`,
				MAX(`pnow_`) OVER ( PARTITION BY `pnum_`, `bnum_`,`inum_`, `pslct`, `enum_`) AS `mxpnw`,
				MAX(`gnum_`) OVER ( PARTITION BY `pnum_`, `bnum_`,`inum_`, `pslct`, `enum_`) AS `mxgnw`
			FROM
				`flow_a120tbl`
		)
SELECT
	`_sid_`,
	 COUNT(`pmony` <> 0 or NULL) AS `_evq_`,
	 SUM( CASE `pslct` WHEN '材料' THEN `pmony` ELSE 0 END ) AS `smmtm`,
	 SUM( CASE `pslct` WHEN '加工' THEN `pmony` ELSE 0 END ) AS `smprm`
	
FROM
	`maxq`
WHERE
	`pnow_` = `mxpnw` AND `gnum_` = `mxgnw`
;

やりたいこと

• 材料費と加工費の計算（レコードごとの合計）を行う前に、pnow_とgnum_の値が最大値と同じレコードを抽出し、材料費と加工費の集計を行いたいです。

入力されるデータ例

管理No	品名	費用区分	費用金額	現行工程	最終工程	グループコード
K01	りんご	材料費	50	02	16	02
K01	りんご	材料費	50	12	16	09
K01	りんご	加工費	150	12	16	09
K01	りんご	材料費	50	12	16	11
K01	りんご	加工費	150	12	16	11

現在の出力データ例

管理No	品名	材料費合計	加工費合計	現行工程	最終工程	グループコード
K01	りんご	100	300	12	16	11

※材料費と加工費の合計を出す際、現在工程pnow_が最大値のレコードだけを抽出しているため、グループコードgnum_が最大値の時も抽出条件に加えないと凡例のデータで抽出した場合、２レコード分の合計を計算してしまっている

出力させたいデータ例

管理No	品名	材料費合計	加工費合計	現行工程	最終工程	グループコード
K01	りんご	50	150	12	16	11

Comments

[yambejp]

view以前にテーブルとそのサンプルを例示してもらわないと・・・

[hope_mucci]

ソースの列名と入力データ名の列名が合致していないのでちんぷんかんぷんです。
どっちかに合わせてください。
あと、「やってみたこと」でgroup byを消した理由はなんでしょう？消さなければうまくいくのでは？

Answer 1

いろいろチャレンジした結果、自己解決できました。

WITHを使って2段階でのクエリを行っていました。これが原因みたいになっていました。

解決した方法は・・・
0. 最初に現行値（pnow_）の値の最大値を見つけだし、pnow_とpnow_の最大値が一緒になるレコードを抽出するビューを作成
0. 前項の結果をもとに、ｸﾞﾙｰﾌﾟｺｰﾄﾞ（gnum_）の最大値を見つけ出し、gnum_とgnum_の最大値が一緒になるレコードを抽出

CREATE VIEW `flow_a120_max_pnow`  AS
SELECT
	*,
	CONCAT ( `pnum_`, `bnum_` , `inum_` ) AS `_sid_`,
	MAX(`pnow_`) OVER ( PARTITION BY `pnum_`, `bnum_`,`inum_`, `pslct`, `enum_`) AS `mxpnw`
FROM
	`flow_a120tbl`
;


CREATE VIEW `flow_a120_gc_pnow`  AS
SELECT
	*,
	MAX(`gnum_`) OVER ( PARTITION BY `pnum_`, `bnum_`,`inum_`, `pslct`, `enum_`) AS `mxgnm`
FROM
	`flow_a120_max_pnow`
WHERE
	`pnow_` = `mxpnw`
;


CREATE VIEW `flow_a120_sum_view`  AS
SELECT
	`_sid_`,
	 COUNT(`pmony` <> 0 or NULL) AS `_evq_`,
	 SUM( CASE `pslct` WHEN '材料' THEN `pmony` ELSE 0 END ) AS `smmtm`,
	 SUM( CASE `pslct` WHEN '加工' THEN `pmony` ELSE 0 END ) AS `smprm`
	
FROM
	`flow_a120_gc_pnow`
WHERE
	`gnum_` = `mxgnm`
GROUP BY
	`pnum_`,
	`bnum_`,
	`inum_`,
	`gnum_`
;

いろいろご指導いただきありがとうございました。

きっと考えた私の方法よりも、皆様から教えていただいたやり方の方がスマートかつ高速なのかもしれませんが、今後自分で触ったときにわからなくならないようにするためにも、この方法で進めていこうと思います。

Answer 2

SQL:2003 のウィンドウ関数を MariaDB 10.2 で試す
ROW_NUMBER() OVER(PARTITION BY ...
をインラインビューで記述、あとは分かるかと。

なお、SQLで使われるテーブルはCREATE TABLEされている必要があります。質問のテーブル定義の説明もCREATE TABLEに変更。SELECTされるデータもINSERTされている必要があります。INSERT文でデータを提示できた方が実際にSQLを実行できるので適切なコメントが付き易いのでは？

Answer 3

現在工程pnow_が最大値のレコードだけを抽出しているため、グループコードgnum_が最大値の時も抽出条件に加えないと

pnow_の最大は、gnum_が同じもののうちの最大でないと駄目なんじゃないでしょうか。
試されたものはそれぞれ独立しています。

(pnum_, bnum_,inum_, pslct, enum_)がそれらの区切りなら、それも条件に加えないと駄目なんでは。

追記

最大のgnum_のうち最大のpnow_に関する集計なら以下のようになるかと思います。
仕様的にあっているなら、分析関数を使用するまでもありません。
※件数の箇所はNUllの扱いが正しくなかったので変更しています。

SELECT
     CONCAT ( `pnum_`, `bnum_` , `inum_` ) AS `_sid_`,
     COUNT((COALESCE(`pmony`, 0) <> 0)) AS `_evq_`,
     SUM( CASE `pslct` WHEN '材料' THEN `pmony` ELSE 0 END ) AS `smmtm`,
     SUM( CASE `pslct` WHEN '加工' THEN `pmony` ELSE 0 END ) AS `smprm`

FROM `flow_a120tbl` t1
WHERE
      `gnum_` = (select Max(`gnum_`) from `flow_a120tbl`) 
  AND `pnow_` = (select Max(`pnow_`) from `flow_a120tbl` where `gnum_` = t1.`gnum`)
GROUP BY
    `pnum_`,
    `bnum_`,
    `inum_`