INNER JOINでサブテーブルの最大レコードをくっ付ける
メインテーブルに関連付くサブテーブルのうち
サブのIDが最大の情報を取ってくる場合のSQL
select a.*,c.* from main_table a inner join ( select main_id,max(sub_id) sub_id from sub_table group by main_id ) b on a.main_id = b.main_id inner join sub_table c on c.sub_id = b.sub_id ;
テーブルとINSERT文
CREATE TABLE "main_table" ( "main_id" INTEGER, "main_info" TEXT ); CREATE TABLE "sub_table" ( "main_id" INTEGER, "sub_id" INTEGER, "write_date" INTEGER, "sub_info" TEXT ); insert into main_table values (1,"main_1"); insert into main_table values (2,"main_2"); insert into sub_table values(1,1,20220101,"sub_1_1"); insert into sub_table values(1,2,20220102,"sub_1_2"); insert into sub_table values(1,3,20220103,"sub_1_3"); insert into sub_table values(2,4,20220104,"sub_2_1"); insert into sub_table values(2,5,20220105,"sub_2_2"); insert into sub_table values(2,6,20220106,"sub_2_3");
Update文内でCaseを使う
update_col1 =
case when tbl1.datecolumn <= '20210101' then
tbl1.value1
else
tbl1.value2
end
結合条件内でcaseを使う
検証はSQLiteでやっているので他の環境でも使えるかと
テーブル作成
CREATE TABLE "subtable" ( "date1" TEXT, "date2" TEXT, "date3" TEXT, "flg" TEXT ); CREATE TABLE "maintable" ( "dateval" TEXT, "value" TEXT );
データ投入
insert into subtable ('date1','date2','date3','FLG') values ('20210101','20210201','20210301','1');
insert into subtable ('date1','date2','date3','FLG') values ('20210103','20210203','20210303','2');
insert into subtable ('date1','date2','date3','FLG') values ('20210104','20210205','20210305','3');
insert into maintable ('dateval','value') values ('20210101','101');
insert into maintable ('dateval','value') values ('20210203','203');
insert into maintable ('dateval','value') values ('20210305','305');
commit;
FLGの値によってmaintableの条件を切り替える
select maintable.value,subtable.flg,maintable.dateval from maintable inner join subtable on 1 = 1 and case when subtable.FLG = '1' THEN maintable.dateval = subtable.date1 when subtable.FLG = '2' THEN maintable.dateval = subtable.date2 ELSE maintable.dateval = subtable.date3 end
以下のようなデータが出力される
| value | flg | dateval |
|---|---|---|
| 101 | 1 | 20210101 |
| 203 | 2 | 20210203 |
| 305 | 3 | 20210305 |
t4g.nanoのUnixBench
CPUはARM
Dhrystone 2 using register variables 69256536.7 lps (10.0 s, 7 samples)
Double-Precision Whetstone 14140.0 MWIPS (9.9 s, 7 samples)
Execl Throughput 8923.3 lps (30.0 s, 2 samples)
File Copy 1024 bufsize 2000 maxblocks 1027796.4 KBps (30.0 s, 2 samples)
File Copy 256 bufsize 500 maxblocks 275578.2 KBps (30.0 s, 2 samples)
File Copy 4096 bufsize 8000 maxblocks 2719670.1 KBps (30.0 s, 2 samples)
Pipe Throughput 3744424.2 lps (10.0 s, 7 samples)
Pipe-based Context Switching 406329.6 lps (10.0 s, 7 samples)
Process Creation 14183.2 lps (30.0 s, 2 samples)
Shell Scripts (1 concurrent) 13104.1 lpm (60.0 s, 2 samples)
Shell Scripts (8 concurrent) 2002.9 lpm (60.0 s, 2 samples)
System Call Overhead 3183410.4 lps (10.0 s, 7 samples)
System Benchmarks Index Values BASELINE RESULT INDEX
Dhrystone 2 using register variables 116700.0 69256536.7 5934.6
Double-Precision Whetstone 55.0 14140.0 2570.9
Execl Throughput 43.0 8923.3 2075.2
File Copy 1024 bufsize 2000 maxblocks 3960.0 1027796.4 2595.4
File Copy 256 bufsize 500 maxblocks 1655.0 275578.2 1665.1
File Copy 4096 bufsize 8000 maxblocks 5800.0 2719670.1 4689.1
Pipe Throughput 12440.0 3744424.2 3010.0
Pipe-based Context Switching 4000.0 406329.6 1015.8
Process Creation 126.0 14183.2 1125.7
Shell Scripts (1 concurrent) 42.4 13104.1 3090.6
Shell Scripts (8 concurrent) 6.0 2002.9 3338.2
System Call Overhead 15000.0 3183410.4 2122.3
========
System Benchmarks Index Score 2456.8
Raspberry Pi 4 のUnixBench
メモリ4GB Ubuntu 20.04
Dhrystone 2 using register variables 15984419.4 lps (10.0 s, 7 samples)
Double-Precision Whetstone 2626.3 MWIPS (10.0 s, 7 samples)
Execl Throughput 403.7 lps (29.9 s, 2 samples)
File Copy 1024 bufsize 2000 maxblocks 86486.0 KBps (30.0 s, 2 samples)
File Copy 256 bufsize 500 maxblocks 23530.0 KBps (30.0 s, 2 samples)
File Copy 4096 bufsize 8000 maxblocks 244801.6 KBps (30.0 s, 2 samples)
Pipe Throughput 119899.2 lps (10.0 s, 7 samples)
Pipe-based Context Switching 25633.5 lps (10.0 s, 7 samples)
Process Creation 2739.9 lps (30.0 s, 2 samples)
Shell Scripts (1 concurrent) 1496.9 lpm (60.1 s, 2 samples)
Shell Scripts (8 concurrent) 357.8 lpm (60.3 s, 2 samples)
System Call Overhead 159364.7 lps (10.0 s, 7 samples)
System Benchmarks Index Values BASELINE RESULT INDEX
Dhrystone 2 using register variables 116700.0 15984419.4 1369.7
Double-Precision Whetstone 55.0 2626.3 477.5
Execl Throughput 43.0 403.7 93.9
File Copy 1024 bufsize 2000 maxblocks 3960.0 86486.0 218.4
File Copy 256 bufsize 500 maxblocks 1655.0 23530.0 142.2
File Copy 4096 bufsize 8000 maxblocks 5800.0 244801.6 422.1
Pipe Throughput 12440.0 119899.2 96.4
Pipe-based Context Switching 4000.0 25633.5 64.1
Process Creation 126.0 2739.9 217.5
Shell Scripts (1 concurrent) 42.4 1496.9 353.0
Shell Scripts (8 concurrent) 6.0 357.8 596.4
System Call Overhead 15000.0 159364.7 106.2
========
System Benchmarks Index Score 231.9
Bitbucketからクローン
git clone https://user@bitbucket.org/user/repository.git
