【SQL】件数を抽出し、1～スタートで文字列結合したい。

Question

SQLについてアドバイス頂けますでしょうか。
RDBMSは、oracleを使用しています。

添付画像のように、同商品番号・同店舗コードで、発注日の件数を
商品番号の最後に「_件数」のような形で表現したいです。

＜実施したこと＞
以下のようなSQLを書いてみました。

SELECT 商品番号,
COUNT（商品番号）,
店舗コード,
発注日
FROM　 テーブルA

COUNT関数で、件数を抽出し、商品番号を件数を文字列結合すればいいのかと考えたのですが
「単一グループのグループ関数ではありません。」とのエラー。
そもそも件数を抽出できたとしても、その件数がお尻についたところで
私がやりたい形にはならない事がわかりました。
⇒例えば、添付画像では商品番号「012348」が3件ありますが
COUNT関数で抽出できたとしても、「012348_3」が並んでしまい
１～のスタートでは無い。

もし、良い案がありましたらアドバイス頂けると幸いです。
よろしくお願いいたします。

Answer 1

たとえば以下のようなSQLで実現可能です。

SELECT 
  商品番号 || '_' || ROW_NUMBER() OVER (PARTITION BY 商品番号, 店舗コード ORDER BY 発注日) 商品番号, 
  店舗コード, 
  発注日
FROM テーブルA

以下は稼働確認用のDDLです。

CREATE TABLE テーブルA(
  商品番号   CHAR(6) NOT NULL,
  店舗コード CHAR(6) NOT NULL,
  発注日     DATE    NOT NULL
);
INSERT INTO テーブルA VALUES ('012345', '0001',TO_DATE('2022/04/29', 'YYYY/MM/DD'));
INSERT INTO テーブルA VALUES ('012346', '0001',TO_DATE('2022/05/12', 'YYYY/MM/DD'));
INSERT INTO テーブルA VALUES ('012347', '0001',TO_DATE('2022/06/25', 'YYYY/MM/DD'));
INSERT INTO テーブルA VALUES ('012348', '0001',TO_DATE('2022/07/01', 'YYYY/MM/DD'));
INSERT INTO テーブルA VALUES ('012348', '0001',TO_DATE('2022/07/21', 'YYYY/MM/DD'));
INSERT INTO テーブルA VALUES ('012348', '0001',TO_DATE('2022/07/30', 'YYYY/MM/DD'));

Comments

[Ponpon.981]

コメントありがとうございます。
やりたかったことが実現できました！
PARTITION BYは初めてお聞きするものでしたので、大変勉強になりました。

Answer 2

とりあえずMySQLで書いておきますので適当に調整してください

create table tbl(id int primary key,code varchar(10));
insert into tbl values
(1,'012345'),
(2,'012346'),
(3,'012347'),
(4,'012348'),
(5,'012348'),
(6,'012348');
select id,concat(code,'_',(select count(*) from tbl where code=t1.code and id<=t1.id))
from tbl as t1

Comments

[Ponpon.981]

早速のコメントありがとうございます。
参考にさせて頂きます。