[過去ﾛｸﾞ] SQL質疑応答スレ 19問目 (982ﾚｽ)
上下前次1-新
このｽﾚｯﾄﾞは過去ﾛｸﾞ倉庫に格納されています｡
次ｽﾚ検索 歴削→次ｽﾚ 栞削→次ｽﾚ 過去ﾛｸﾞﾒﾆｭｰ
52(1): 2019/09/01(日)09:35 ID:??? AAS
date player score weekAve
2019-09-01 A 80 ?
2019-09-01 B 67 ?
2019-09-01 C 91 ?
2019-08-31 A 78 ?
2019-08-31 B 65 ?
2019-08-31 C 93 ?
2019-08-30 A 81 ?
2019-08-30 B 69 ?
2019-08-30 C 90 ?
省3
53(1): 2019/09/01(日)16:47 ID:??? AAS
sqliteで作ったので適当になおしてくれ
select tb1.col1,tb1.col2,max(tb1.col3),avg(tb2.col3)
from tb1
left outer join tb1 as tb2
on tb2.col2=tb1.col2
and date(tb2.col1)>=date(tb1.col1, '-7 days')
and date(tb2.col1)<=date(tb1.col1)
group by tb1.col1,tb1.col2
;
54: 52 2019/09/01(日)17:52 ID:??? AAS
>>53
大変ありがとうございました。
使わせていただきます。
55(1): 2019/09/20(金)14:25 ID:??? AAS
name, datetime, comment のテーブルでnameとdatetime で主キーとしています。
nameが重複しているものだけをリストアップするにはどうすればよいでしょうか。
A? 2019/9/20 0:0? AAA1
B? 2019/9/20 0:5? BBB1
C? 2019/9/20 0:5? CCC1
A? 2019/9/20 1:0? AAA2
C? 2019/9/20 1:5? CCC2
A? 2019/9/20 2:0? AAA3
の場合以下を期待します。
A? 2019/9/20 0:0? AAA1
省13
56(4): 2019/09/20(金)16:17 ID:??? AAS
HAVING使うぐらいか？
SELECT * FROM xxx WHERE name IN
(
SELECT name FROM xxx GROUP BY name HAVING 1<COUNT(*)
)
ORDER BY name, datetime
57: 2019/09/20(金)16:43 ID:??? AAS
>>56
出来ました！
group by はhaving でしたね。
ありがとうございました。
58(4): 2019/09/20(金)18:25 ID:??? AAS
こんなんでいいだろ
select *
from xxx T1
where exists (
select *
from xxx T2
where T1.name = T2.name
and T1.datetime <> T2.datetime
)
59: 2019/09/21(土)16:28 ID:??? AAS
これはありえないw
60: 2019/09/21(土)17:16 ID:??? AAS
テーブルのレコード数が多くてnameのカーディナリティも十分高いなら
一般には>>56より>>58の方が良いだろ。
61(3): 2019/09/21(土)19:24 ID:8nH6piRt(1) AAS
DBMSとデータ量で実測してみないとわからんな。
NoSQL系なら58の方が速そう
RDBなら56を改変して
SELECT L.*
FROM xxx L
JOIN (SELECT name FROM xxx GROUP BY name HAVING 1<COUNT(*)) R
ORDER BY L,name, L,datetime
の方が速いかも
62: 2019/09/21(土)20:59 ID:??? AAS
日時が同じデータはありえないという条件をいわれてないなら
普通はこんなsqlはかけないわw
63: 2019/09/21(土)21:17 ID:??? AAS
> nameとdatetime で主キーとしています。
64: 2019/09/21(土)21:52 ID:??? AAS
>>61
環境によるから実際に実行計画をみてみなきゃ確実なことは言えないのはその通りだけど、
そのsqlはサブクエリでfull scan 1回、外側で1回、nameのカーディナリティが高ければ
中間データも大きくなり、しかもJOINにインデックスが使われないときているから、
メモリに入りきらないようなテーブルの場合は極端に遅くなりそう。
65: 2019/09/21(土)22:28 ID:??? AAS
nameのカーディナリティが高い場合、>>56や>>61では中間データが大きくなり
メモリに乗り切らないことが考えられ、>>58のほうが速い可能性がある。
nameのカーディナリティが低い場合、>>58ではexistsの条件に早くひっかかる
可能性が大きくなり、やはり>>58のほうが速い可能性がある。
66: 2019/09/21(土)22:58 ID:??? AAS
>>58は相関サブクエリにインデックスが使われ、datetimeの比較は2エントリ目で
必ず判断がつくから、実のところ速度はカーディナリティにあまり影響されない。
67(1): 2019/09/22(日)04:36 ID:??? AAS
PostgreSQL 9.6.11 にて1000000行のデータでEXPLAIN ANALYZEを付けて実行してみた。データは https://ideone.com/WTthGi のようにして作成。
各8回実行し、最初の4回は捨てた。
>>55 PostgreSQLでは副問合せに名前を付けないとエラーになるため AS a を追加
Execution time: 1202.286 ms / 1173.974 ms / 1194.647 ms / 1221.041 ms
>>56
Execution time: 1164.661 ms / 1171.337 ms / 1210.060 ms / 1179.993 ms
>>58 order by name, datetimeを追加
Execution time: 2350.302 ms / 2320.161 ms / 2345.047 ms / 2368.932 ms
>>61 R の後にON L.name=R.nameを追加、ORDER BYの,を.に変更
Execution time: 1248.337 ms / 1215.495 ms / 1222.694 ms / 1222.818 ms
省1
68: 2019/09/22(日)11:03 ID:oC+qLfZI(1) AAS
>>67
素晴らしい。結局最初のであまり問題ないね
最近はよっぽど遅い場合を除いて
意図が分かるように書くのがいい感じよね
69(2): 2019/10/01(火)22:47 ID:yfemt3Lz(1) AAS
すいません
教えてください…
カラムは2つです(アクセスログです)
IP
CGI
複数種類あるCGIに対してどのCGIに
このIPからは何回、このIPからは何回アクセスされたというのを集計したいのですが
SQLが思いつきません…
だれか教えてください…
70: 2019/10/01(火)23:20 ID:??? AAS
>>69
一旦各CGIがLogに出力して
日替わりや週替わり月替わりで集計したらどうか
71: 2019/10/02(水)09:28 ID:??? AAS
>>69 select CGI,IP,count(*) from テーブル group by CGI,IP;
上下前次1-新書関写板覧索設栞歴
あと 911 ﾚｽあります
ｽﾚ情報 赤ﾚｽ抽出 画像ﾚｽ抽出 歴の未読ｽﾚ AAｻﾑﾈｲﾙ
ぬこの手 ぬこTOP 0.284s*