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

Question

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

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

user_id	value
1	1.2
1	1.2
2	1.1
3	2.3
3	2.3
4	1.2

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

user_id	value
1	1.2
2	1.1
3	2.3
4	1.2
5	1.3

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

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

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

SELECT SUM(m.value)
FROM master_table m
WHERE EXISTS(
  SELECT user_id
  FROM log_table l
  WHERE l.user_id = m.user_id
)

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

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

Answer 1

これで良さそうですね

select sum(value)
from (select distinct user_id,value
      from log_table)

Answer 2

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

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

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

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

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

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

ALTER TABLE log_table
 ADD INDEX IX_LOG_USER( user_id )
;

ALTER TABLE master_table
 ADD INDEX IX_MASTER_USER_VALUE( user_id, value )
;

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

ALTER TABLE log_table
 ADD COLUMN id bigint NOT NULL AUTO_INCREMENT
,ADD PRIMARY KEY( id )
,ADD INDEX IX_LOG_USER_VALUE( user_id, value )
,ADD INDEX IX_LOG_VALUE( value )
;

SELECT	SUM( T1.value )
FROM	log_table T1
WHERE	T1.id	= (
					SELECT	MAX( T2.id )
					FROM	log_table T2
					WHERE	T2.user_id	= T1.user_id
					)
;

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

Answer 3

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

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

SELECT SUM(JT.[value])
FROM (
  SELECT DISTINCT
    RT.[user_id]
  , [master_table].[value]
  FROM (
    SELECT DISTINCT [user_id] FROM [raw_table] -- 生テーブル(マスターとJOIN前)
    ) RT
  LEFT JOIN [master_table]
  ON RT.[user_id] = [master_table].[user_id]
) JT

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

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

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

Answer 4

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

select
	sum(value)
from 
	(select 
		user_id, 
		value 
	from 
		log_table 
	group by 
		user_id, 
		value)

Comments

[退会済みユーザー]

DBMSの仕様で結果が変わるかもしれませんよ。

※ 浮動小数点数まわり