質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

1回答

533閲覧

SQLで平均利用回数を求めたい

yskman

総合スコア2

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

0クリップ

投稿2022/11/24 02:46

実現したいこと

以下のテーブルがあり
|ユーザーID|施設利用日|バス利用|
|001|10/1|なし|
|001|10/2|あり|
|001|10/3|なし|
|001|10/4|なし|
|002|10/1|なし|
|002|10/2|なし|
|003|10/3|なし|
|003|10/4|なし|
|004|10/1|あり|
|004|10/4|なし|
|004|10/5|なし|
バス利用ありのユーザーと利用なしのユーザの平均施設利用回数を取得したいです

総利用回数/ユニーク利用者数で計算しようと考えていまして
001は4回利用
002は2回利用
003は2回利用
004は3回利用

バス利用グループの平均施設利用回数は
7/2=3.5
バス未利用グループの平均施設利用回数は
4/2=2

上記を算出するための分母と分子をSQLで拾いたいのですが
利用ありグループは
select count(distinct(id)) from tbl where バス利用=あり
は2件で欲しい値となりますが、利用なしグループは
select count(distinct(id)) from tbl where バス利用=なし
では4件となってしまいます
以下のようにグルーピングするためにはどのようなsqlを記述すればよいでしょうか?
|001|10/1|なし|
|001|10/2|あり|
|001|10/3|なし|
|001|10/4|なし|
|004|10/1|あり|
|004|10/4|なし|
|004|10/5|なし|

|002|10/1|なし|
|002|10/2|なし|
|003|10/3|なし|
|003|10/4|なし|

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

yambejp

2022/11/24 02:48

SQLの種類とバージョンは何を想定していますか?
yskman

2022/11/24 02:56

SimpWrightからSQLを実行しているのですが、Oracleを想定しています
yambejp

2022/11/24 03:14 編集

たとえば|001|10/3|なし|→|001|10/3|あり|だった場合=001ユーザーがバスを2回利用した場合、計算根拠は7/2→7/3に変わるのでしょうか?全体回数を使用回数でわるか使用人数でわるかあいまいです それと本題とは関係ありませんが、バスとはbusとbathのどちらか気になっています
yskman

2022/11/24 03:39

総利用回数/ユニーク利用者数で計算しようとしているので、7/2のままでかわりません バスはbusです
guest

回答1

0

ベストアンサー

ざっくり(書式はMySQLで書いているので、oracleであれば調整してください)

SQL

1create table tbl(id int(3) unsigned ,d date,bus varchar(10)); 2insert into tbl values 3('001','2022-10-01','なし'), 4('001','2022-10-02','あり'), 5('001','2022-10-03','あり'), 6('001','2022-10-04','なし'), 7('002','2022-10-01','なし'), 8('002','2022-10-02','なし'), 9('003','2022-10-03','なし'), 10('003','2022-10-04','なし'), 11('004','2022-10-01','あり'), 12('004','2022-10-04','なし'), 13('004','2022-10-05','なし');

SQL

1/* バス利用ありのユニーク利用者数 */ 2select count(*) as bus_ari_unique_user from( 3select count(distinct id) 4from tbl 5group by id 6having sum(bus='あり')>0 7) as tmp; 8 9/* バス利用ありの延べ利用者数 */ 10select sum(cnt) as bus_ari_total_user from( 11select count(id) as cnt 12from tbl 13group by id 14having sum(bus='あり')>0 15) as tmp; 16 17/* バス利用なしのユニーク利用者数 */ 18select count(*) as bus_nasi_unique_user from( 19select count(distinct id) 20from tbl 21group by id 22having sum(bus='あり')=0 23) as tmp; 24 25/* バス利用なしの延べ利用者数 */ 26select sum(cnt) as bus_nasi_total_user from( 27select count(id) as cnt 28from tbl 29group by id 30having sum(bus='あり')=0 31) as tmp

投稿2022/11/24 06:08

yambejp

総合スコア114585

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

yskman

2022/11/24 06:54

回答ありがとうございます ちなみにSQLはRedshiftでした 五月雨の質問ですみません bus varchar(10)→ bus int(1)とし、あり/なしを0/1にした場合、 sumの記述の仕方は having sum(bus='0')>0やhaving sum(bus='1')>0で通ると思ったのですが、エラーになってしまいました。 Redshiftだとこの記載はできないのでしょうか?
yskman

2022/11/24 08:43

解決しましたありがとうございました
yambejp

2022/11/24 08:45

ちなみにsum(bus='0')的な書き方はmysqlの特徴ですね 標準SQLですと「sum(case bus when 0 then 1 else 0 end)」のような 書き方になります。 ただし回答にも書いた通り「あり」を含むかどうかをチェックするには busが1であることをチェックするしかありません
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問