カテゴリー別アーカイブ: sql

[postgres]ポストグレスにて数値変換の怪現象(数値を変換した文字に空白の符号が入る)

数値を文字型にして比較したが文字と同値にならない↓

select (to_char(9, '9999') = '9' ) result;

result
--------
f

以前にも似たようなことにつまづいた気がする。しばし、考える。。。

思い出すして下記を実行。

select (to_char(9, '9999') = ' 9' ) result;

result
--------
t

上と何が違うか…符号が空白で出てきているので、
9を変換すると' 9'になるといいうことらしい。

意図した通りにするなら、
to_char関数の第二引数文字列の左側にFMを付与する。
これは字詰めモードで符号を埋めてしまうためのもの。

select (to_char(9, 'FM9999') = '9' ) result;

result
--------
t

これで思った通りの結果になる。

[postgresql]group by して、存在しない値のレコードを出して集計する方法

test_bのテーブル内データを集計して出力したいが、Bというデータのカウント数も出したいとする。

select * from test_a;
id | data
----+------
1 | A
2 | B
3 | C
4 | D

select * from test_b;
data
------
A
A
C
D
D
D

単純にgroup by しても、group by は存在しないレコードはしゅつりょくしないため、
下記のようにBが歯抜けの状態になってしまう。

select count(*),data from test_b group by data;
count | data
-------+------
3 | D
1 | C
2 | A

これを防ぐためにdataの種別を網羅したtest_aという、
dataのマスタテーブルを連結して出力すれば目的の結果が出る。



select a.data, COALESCE(b.cnt,0) cnt from test_a a 
left outer join (select count(*) as cnt, data from test_b group by data) as b on a.data = b.data;


結果は以下の通り

select a.data, COALESCE(b.cnt,0) cnt from test_a a left outer join (select count(*) as cnt, data from test_b group by data) as b on a.data = b.data;
data | cnt
------+-----
A | 2
B | 0
C | 1
D | 3

実務ではこうも単純ではない。
複数のテーブルに正規化されているはずなので、応用が必要である。

ABCD評価をされる5つのクラス(クラス1、クラス2、クラス3、クラス4、クラス5)のdataとして区別される場合はどうなるだろうか?

select * from test_c;
id | class_name
----+------------
1 | class1
2 | class2
3 | class3
4 | class4
5 | class5

test_bのカラムにクラスのcolumnが追加されてこうなったらどう集計しなくてはならないか?
alter table test_b add column class_id integer default 0;

select * from test_b;
data | class_id
------+----------
A | 1
A | 1
A | 2
A | 3
A | 5
B | 1
B | 3
C | 3
C | 4
C | 5
D | 1
D | 1
D | 3
D | 3
D | 5
D | 5

こうなってくるとクロス集計を出す方が人間的には嬉しいかもしれないが、
まずはローデータとしてずらずらと並べてカウントアップするとこうなると思う。

select x.class_name, x.data, coalesce(b.cnt,0) as cnt from
 (select a.data, c.id, class_name from test_a a, test_c c) as x
 left outer join (select count(*) as cnt, data, class_id
      from test_b group by data,class_id) as b
        on x.data = b.data and x.id = b.class_id order by id;

※classsごとにdataがa~d評価されるという情報がないので、
 inner joinしてから連結することになっており見苦しいので、
 工夫の余地がある。

class_name | data | cnt
------------+------+-----
class1 | A | 2
class1 | B | 1
class1 | C | 0
class1 | D | 2
class2 | A | 1
class2 | B | 0
class2 | C | 0
class2 | D | 0
class3 | A | 1
class3 | B | 1
class3 | C | 1
class3 | D | 2
class4 | A | 0
class4 | B | 0
class4 | C | 1
class4 | D | 0
class5 | A | 1
class5 | B | 0
class5 | C | 1
class5 | D | 2

相関サブクエリ

外部クエリの影響を受けるサブクエリを相関クエリと言うが、そもそもサブクエリは外部クエリの影響を受けて作るのが一般的ではないか?外部の影響を受けないならもはやサブではないのではないか。複数のクエリを同時に実行しているだけではないか?では、相関サブクエリの対義語ってなんだ?相関がないサブクエリ?意味がよくわからないな。

それだと相関でもサブでもなく、独立クエリが並列して(実行されているわけではないが)結果が2つ買ってくるだけになる。親も子もない。たまたま記述された関係でどちらが入れ子になっているかという違いしかない。たまごのなかににわとりがいるか、にわとりのなかにたまごがあるか、という違いでしかないということだ。こうなってくると親子関係や主従関係はない。したがって、サブクエリとはすなわち相関サブクエリであると思うのだが、ほかに言葉があるのか?おしえてください。頭のいい人。

[SQL]特定のテーブルの特定のカラムをn倍する方法

以下のSQLでうまくいった。

update m_table
set col1 =
(select (b.col1 * 10) s10
from m_table b
where m_table.id = b.id
);

もっとうまい方法があるのかもしれないが、
SQLだけで集計計算する方法を模索していて、
思いついたselect-update文の1つ。

各レコードにIDが振られている前提で書いている。

IDが無いならば別の方法をとらないといけないかも。