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

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

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

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

Q&A

解決済

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

yskman
yskman

総合スコア2

SQL

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

1回答

0グッド

0クリップ

260閲覧

投稿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|なし|

以下のような質問にはグッドを送りましょう

  • 質問内容が明確
  • 自分も答えを知りたい
  • 質問者以外のユーザにも役立つ

グッドが多くついた質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

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

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

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

下記のような質問は推奨されていません。

  • 間違っている
  • 質問になっていない投稿
  • スパムや攻撃的な表現を用いた投稿

適切な質問に修正を依頼しましょう。

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です

回答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

総合スコア109141

良いと思った回答にはグッドを送りましょう。
グッドが多くついた回答ほどページの上位に表示されるので、他の人が素晴らしい回答を見つけやすくなります。

下記のような回答は推奨されていません。

  • 間違っている回答
  • 質問の回答になっていない投稿
  • スパムや攻撃的な表現を用いた投稿

このような回答には修正を依頼しましょう。

回答へのコメント

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であることをチェックするしかありません

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

ただいまの回答率
86.02%

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

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

質問する

関連した質問

同じタグがついた質問を見る

SQL

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