SQLで条件を使い、割合を出したい。

Question

SQLを使用し、下記①～③を割り出したいのですが、可能でしょうか。
不可能でしたらプログラムで作成しようと思います。
使用DB:ACCESS　SQLクライアント：CSE

①初回でサンプルを買う人（初回のうち〇％）　
②初回から現品を買う人（初回のうち〇％）　
③サンプルから現品に至る人（サンプル買った人のうち〇％）

テーブルA

売上ID、受注日、名前、商品名、
10001 2019-05-01 鈴木 AAAAAサンプル
10001 2019-05-01 鈴木 BBBBBBB
10002 2019-05-05 佐藤 DDDDDDD
10003 2019-06-12 井上 DDDDDDD
10003 2019-06-12 井上 EEEEEサンプル
10004 2019-07-10 田中 CCCCCサンプル
10004 2019-07-10 田中 EEEEEサンプル
10004 2019-07-10 田中 GGGGGGG
：：：

※「サンプル」と記載がないものが現品とする。

Comments

[退会済みユーザー]

DB サーバーは何ですか？ SQL Server? MySQL? Oracle? その他？
SQL はサーバーによって異なります（方言的なものですが）。SQL の質問をするならサーバーが何かを書きましょう。

[caprice]

ありがとうございます。
ACCESSになります。SQLクライアントはCSEを使用しています。
宜しくお願い致します。

[退会済みユーザー]

それを質問を編集してなるべく質問文で目立つ場所に追記していただければと思います。

[caprice]

追記させていただきました。
宜しくお願い致します。

[sazi]

同じ商品のサンプル/現品を識別する情報は無いのですか？
商品名から、現品とその現品のサンプルを識別するのは面倒ですよ。

[caprice]

ありがとうございます。
元データがその様になってしまっておりまして、
変更ができない形となっております。
宜しくお願い致します。

Answer 1

先ずは現品とサンプルを結びつける情報のクエリーを作成します。
[Q_商品名リスト]

select replace(商品名, 'サンプル', '') as 商品名グループ
     , (商品名 like '*サンプル*') as サンプル, 商品名
from テーブルA
group by replace(商品名, 'サンプル', ''), (商品名 like '*サンプル*'), 商品名

以下は上記を元にした件数です。商品ごとの初回購入件数は①＋②です。

①初回でサンプルを買う人

select  商品名グループ, count(*)
from テーブルA ta inner join Q_商品名リスト q1 on ta.商品名=q1.商品名
where 受注日=(select min(受注日) from テーブルA where 名前=ta.名前)
  and サンプル
group by 商品名グループ

②初回から現品を買う人

select ta.商品名, count(*)
from テーブルA ta inner join Q_商品名リスト q1 on ta.商品名=q1.商品名
where 受注日=(select min(受注日) from テーブルA where 名前=ta.名前)
  and Not サンプル
group by ta.商品名

③サンプルから現品に至る人

select  商品名グループ, count(*)
from テーブルA ta inner join Q_商品名リスト q1 on ta.商品名=q1.商品名
where 受注日=(select min(受注日) from テーブルA where 名前=ta.名前)
  and サンプル
  and exists(select 1 from テーブルA where 名前=ta.名前 and 商品名=q1.商品名グループ)
group by 商品名グループ

正規化もされていないですし、遅いのは覚悟して下さい。
件数次第では安定しないかもしれません。
その際はQ_商品名リストを元にテーブルにした方が安定するでしょうし、多少早くなるかと思います。

Comments

[caprice]

ありがとうございました！！
１つ１つ確認して、しっかり学ばせていただきます。
大変お手数をおかけいたしました。

Answer 2

初回とはなにかという定義によります

• 同じ売上IDであれば全部初回なのか
• 最初のものだけが初回なのか

が、わかりません。SQLの場合レコードの順番というのはあてにならないので
後者の場合は別途順番を確定するためのカラムが必要です

• 商品名の「サンプル」という文字も末尾にしかつかないのか
• 先頭や途中にもつくのか

で、書き方がかわってきます
もしちゃんと管理したいなら、サンプルカラムをつくってそのon/offで
きちんとサンプルかどうか判別できるようにしたほうが効率的です

sample

アクセスと書式が違ったらうまく読み替えてください

create table tbl(pid int primary key auto_increment,売上ID int,受注日 date,名前 varchar(20),商品名 varchar(30));
insert into tbl(売上ID,受注日,名前,商品名) values
(10001,'2019-05-01','鈴木','AAAAAサンプル'),
(10001,'2019-05-01','鈴木','BBBBBBB'),
(10002,'2019-05-05','佐藤','DDDDDDD'),
(10003,'2019-06-12','井上','DDDDDDD'),
(10003,'2019-06-12','井上','EEEEEサンプル'),
(10004,'2019-07-10','田中','CCCCCサンプル'),
(10004,'2019-07-10','田中','EEEEEサンプル'),
(10004,'2019-07-10','田中','GGGGGGG'),
(10005,'2019-07-10','吉田','HHHHサンプル'),
(10011,'2019-05-11','鈴木','XXXXXX'),
(10012,'2019-06-05','佐藤','YYYYYサンプル');

• 初回にサンプルを買った人

select * from (
select 売上ID,名前,sum(case when 商品名 LIKE '%サンプル%' then 1 else 0 end) as flg
from tbl
group by 売上ID,名前
) as t1
where not exists(
select 売上ID,名前,sum(case when 商品名 LIKE '%サンプル%' then 1 else 0 end) as flg
from tbl
group by 売上ID,名前
having 名前=t1.名前 and 売上ID<t1.売上ID
)
and flg>0

• 初回に現品を買った人

select * from (
select 売上ID,名前,sum(case when NOT 商品名 LIKE '%サンプル%' then 1 else 0 end) as flg
from tbl
group by 売上ID,名前
) as t1
where not exists(
select 売上ID,名前,sum(case when NOT 商品名 LIKE '%サンプル%' then 1 else 0 end) as flg
from tbl
group by 売上ID,名前
having 名前=t1.名前 and 売上ID<t1.売上ID
)
and flg>0

• サンプルから現品に至る人

ちょっと意味がわからない

Comments

[caprice]

わかりにくく申し訳ありません。
同じ売上IDであれば全部初回となります。
商品名の「サンプル」という文字は末尾以外にも途中にもつくことがあります。
元データがこの様になっており、ややこしく申し訳ありません。

[caprice]

ありがとうございました！！
１つ１つ確認して、しっかり学ばせていただきます。
大変お手数をおかけいたしました。