gourp by して　数字をくっつけながら取り出す

SQL groupby

大分非効率で使い道があまりないロジックを思いついた。




よくあるのが、SQL上で　groyp by しておいて、maxとかsumとかやることがあると思う。
そのときに、足すでもなく最小最大でもなく、
group by の対象となる数字を全部取り出す（数値のまま全部でも、全部文字列結合にしてでも）。




groupbyで使用できるセパレータ付文字列結合メソッドは確かなかったはずで、
（あったらそれを使えばいいさｗ）
大抵はgroupbyせず取り出しておいて、
プログラムでごりごり数値の配列で取り出したり文字列結合したりすると思う。
このときにgroup by するはずだったものをorder byにして順々処理したり、
Java的にいうとMapのListとかにしておいてgroup byのものをキーに、がつがつ詰めたり、
まぁそんなことをするのが定石でしょう。




今回はあえてgroup by する方法を思いついたので、それを書いてみる。


データとしては、こんな感じだろか。

table=test
┌──┬───┐
│LEFT│RIGHT │
├──┼───┤
│   1│     4│
│   1│     5│
│   2│     4│
│   2│     5│
│   3│     4│
│   3│     5│
│   4│     6│
│   4│     7│
│   4│     8│
│   4│     9│
│   4│    10│
│   4│    12│
│   5│     6│
│   5│     7│
│   5│     8│
│   5│     9│
│   5│    10│
│   5│    12│
│   6│    11│
│   7│    11│
│   8│    11│
└──┴───┘

これの　LEFT　に対する　RIGHT　が取り出したいとする


なので一旦RIGHTを2のRIGHT乗する。

SELECT LEFT, RIGHT, POWER( 2, RIGHT ) P FROM TEST

これを副問い合わせにして、
この結果をsumしながらgroup byする
(ついでに見やすいようにleftでorder byする)

SELECT * FROM (
	SELECT LEFT, SUM(P) FROM
    		( SELECT LEFT, RIGHT, POWER( 2, RIGHT ) P FROM TEST )
	GROUP BY LEFT
)
ORDER BY LEFT


結果がこれ

┌──┬───┐
│LEFT│SUM(P)│
├──┼───┤
│   1│48    │
│   2│48    │
│   3│48    │
│   4│6080  │
│   5│6080  │
│   6│2048  │
│   7│2048  │
│   8│2048  │
└──┴───┘

SQL的にはこれでおしまし。
LEFTが1行づつしか出てこないので、これがこいつの利点（？）。


今度はこのSUM(P)をプログラムで分割する。




2の累乗を使用してから加算しているので、
分割してもあいまいにはならず綺麗に分割できる。




分割に使用するのは、
2の0乗(1)
2の1乗(2)
2の2乗(4)
2の3乗(8)
2の4乗(16)
2の5乗(32)
2の6乗(64)
2の7乗(128)
2の8乗(256)
2の9乗(512)
2の10乗(1024)
．．．


この数値で分割すると、
48は32+16
6080は4096+1024+512+256+128+64
2048は2048
から成り立つことがわかる。（わけないよねｗいやわかる人にはわかると思うけど。）




算出方法はロジカルに書くとこうなる。
(2の累乗の値はただ計算すればいいだけなのでそれは問題なく算出できるとする)


1.ターゲットの数値に対して、2の1乗から順に比較する。
2.比較し2のx乗の方がでかくなったら、その数字の1つ前の値を採用する。
(48->64でオーバーなので32、6080->8192でオーバーなので4096、2048->4096でオーバーなので2048)
3.採用した数値が2の何乗なのかを保持しておく
(32=2の5乗、4096=2の12乗、2048=2の11乗)
4.採用した数値で引く。
(48-32=16、6080-4096=1984、2048-2048=0)
5.1.2.3.4.を繰り返し、4.の結果が0になるまで続ける。
(48-32-16=0、6080-4096-1024-512-256-128-64=0、2048-2048=0)
6.保持している値を取り出す
(結果が48だったLEFTの1,2,3は=>5,4、結果が6080だったLEFTの4,5は=>12,10,9,8,7,6、結果が2048だったLEFTの6,7,8=>11)




という感じ。






うーん、2の累乗でごりごり逆算するのがなんとも非効率のような気がする。
2の累乗の結果がintとかlongとかの結果を超えるようなでかい数字は扱えないし。


そもそも
SQLでpowしてリストで取り出している時点で、
group byで複数行にしようが、group byなしで複数行取ろうが、あんまり変わりないよね。
個人的には、group byせずにリストにして取り出して、レコードをループでまわしながら
leftが変わるまでループし変わったら、集めて何か処理に移る　っていうロジックが、
あまり好きじゃないので、何かないかなぁ　って考えてたら、しょうもないのを思いついた。




あまり採用されないレベルだったなｗ