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

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

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

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

Oracle

Oracleは、米オラクルが取り扱うリレーショナルデータベース管理システムです。メインフレームからPCまで、多様なプラットフォームに対応しています。

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

SQL

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

Hive

Hiveとは、Hadoop(オープンソースの大規模分散計算フレームワーク)の上で動作するDWH(Data Warehouse:データウエアハウス)向けのプロダクトです。HiveQLというSQLのような言語で、Hadoop上のデータを操作することができます。

Q&A

4回答

35372閲覧

SQLのSUMでDISTINCTした行だけ集計

txmy

総合スコア7

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

Oracle

Oracleは、米オラクルが取り扱うリレーショナルデータベース管理システムです。メインフレームからPCまで、多様なプラットフォームに対応しています。

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

SQL

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

Hive

Hiveとは、Hadoop(オープンソースの大規模分散計算フレームワーク)の上で動作するDWH(Data Warehouse:データウエアハウス)向けのプロダクトです。HiveQLというSQLのような言語で、Hadoop上のデータを操作することができます。

1グッド

0クリップ

投稿2016/02/21 06:44

###前提・実現したいこと

SQLで集計するときにDISTINCTした行だけSUMをしたいです。
例えば下記のようなログテーブルがあったとします。
log_table

user_idvalue
11.2
11.2
21.1
32.3
32.3
41.2

上記のテーブルはuserのアクセスログを以下の様なマスタにあてて作られたテーブルです。
master_table

user_idvalue
11.2
21.1
32.3
41.2
51.3

ここで上のログテーブルだけからuser_idの重複を除いたvalueの合計を得たいです。

つまり上記であれば
1.2 + 1.1 + 2.3 + 1.2 = 5.8
を得たいです。

下のマスタテーブルを使って

sql

1SELECT SUM(m.value) 2FROM master_table m 3WHERE EXISTS( 4 SELECT user_id 5 FROM log_table l 6 WHERE l.user_id = m.user_id 7)

とすれば得られると思うのですが、master_tableもlog_tableも非常に大きいため、できればすでにjoinしてあるlog_tableだけで完結させたいです。
何か良い方法ないでしょうか?

###補足情報(言語/FW/ツール等のバージョンなど)
Hive(HiveQL)を使っていますが、他のDBでも何か知ってる方法がありましたら、調べる手がかりになりますので、教えていただけると助かります。

nob777👍を押しています

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

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

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

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

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

guest

回答4

0

これで良さそうですね

SQL

1select sum(value) 2from (select distinct user_id,value 3 from log_table)

投稿2016/02/21 08:17

AketiJyuuzou

総合スコア1147

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

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

0

もう解決しましたでしょうか?

「master_tableもlog_tableも非常に大きいため」
とある以上、件数によっては他の回答は性能は出ない可能性がありますが。

今回のテーブルにおいて、distinctは性能面を考慮すると、おすすめできませんね。

「できればすでにjoinしてあるlog_tableだけで完結させたい」
とありますが、質問の内容だけでは、master_tableを参照した方が
どう見ても性能が良いです。
恐らく、質問にない項目を利用したい、という事かとは思いますが。

一応、以下、2つほど方法を回答しておきます。

○方法1
適切なINDEXを貼って、master_tableを参照し、質問に記載されているSQLで実行する。

SQL

1ALTER TABLE log_table 2 ADD INDEX IX_LOG_USER( user_id ) 3; 4 5ALTER TABLE master_table 6 ADD INDEX IX_MASTER_USER_VALUE( user_id, value ) 7;

○方法2
適切なINDEXを貼って、log_tableのみを参照する場合

SQL

1ALTER TABLE log_table 2 ADD COLUMN id bigint NOT NULL AUTO_INCREMENT 3,ADD PRIMARY KEY( id ) 4,ADD INDEX IX_LOG_USER_VALUE( user_id, value ) 5,ADD INDEX IX_LOG_VALUE( value ) 6; 7 8SELECT SUM( T1.value ) 9FROM log_table T1 10WHERE T1.id = ( 11 SELECT MAX( T2.id ) 12 FROM log_table T2 13 WHERE T2.user_id = T1.user_id 14 ) 15;

こちらに環境があるわけではない上に、試してもいないですが、
distinctより方法2の方が性能は良いはずですよ。

投稿2016/08/25 23:01

tomari_perform

総合スコア760

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

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

0

log_tableを作成前なら下記で処理できます。
回答の便宜上、処理前テーブルlog_tableをraw_tableと呼ぶことにします。

下記のSQLにて、
予めraw_tableにdistinctを実施してからmaster_tableとJOINさせるため、
処理時間が短縮されます。
その結果を合計するようになっています。

SQL

1SELECT SUM(JT.[value]) 2FROM ( 3 SELECT DISTINCT 4 RT.[user_id] 5 , [master_table].[value] 6 FROM ( 7 SELECT DISTINCT [user_id] FROM [raw_table] -- 生テーブル(マスターとJOIN前) 8 ) RT 9 LEFT JOIN [master_table] 10 ON RT.[user_id] = [master_table].[user_id] 11) JT 12

どうしても(処理済み)log_tableを使用したい場合は、
AketiJyuuzouさんのSQLが最適解かと思います。

以上、ご参考になりますでしょうか?

P.S.
項目名[value]はタイプミスなどでvaluesとしてしまうと
予約語に引っかかるので避けた方よさそうですね。

投稿2016/02/22 05:53

Aeona

総合スコア396

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

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

0

一つ前の方と似たような答えですが、
GROUP BYでのやり方もあります。

SQL

1select 2 sum(value) 3from 4 (select 5 user_id, 6 value 7 from 8 log_table 9 group by 10 user_id, 11 value) 12

投稿2016/02/21 21:00

ToruHokari

総合スコア33

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

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

退会済みユーザー

退会済みユーザー

2016/08/25 23:36 編集

DBMSの仕様で結果が変わるかもしれませんよ。 ※ 浮動小数点数まわり
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

まだベストアンサーが選ばれていません

会員登録して回答してみよう

アカウントをお持ちの方は

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

ただいまの回答率
85.50%

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

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

質問する

関連した質問