概要

タイトル通り、SQLでデータを成形する際に複数のカラムをkeyとして通し番号がつけたくなりました。
意外とすんなりできる方法が見つかったため、備忘録として残しておきます。
なお筆者は普段Hiveでクエリを流しているため、他とお作法が異なる部分があったら申し訳ないです。

設定

次のようなtestテーブルが存在する場合を考えます。

dt	hour	minute
20190401	0	0
20190401	0	0
20190401	0	10
20190402	4	50
20190402	4	50
20190403	22	40
20190403	22	50

今までの方法

testテーブルに存在するdt, hour, minuteの3つのカラムをkeyとして通し番号が欲しい場合、今まではWITH句を使ってSELECT DISTINCTでdt・hour・minuteを選択し、row_numberで連番を振ったテーブルをJOINしていました(頭の悪い方法なのでクエリは省略)。

今回の方法

通し番号を順番と読み替え、dense_rankを使えば解決することに気がつきました！

serial.sql

SELECT
  dt
  ,hour
  ,minute
  ,dense_rank() OVER (ORDER BY dt, hour, minute) AS serial_number
FROM
  test
ORDER BY
  serial_number

結果はこうなります。

dt	hour	minute	serial_number
20190401	0	0	1
20190401	0	0	1
20190401	0	10	2
20190402	4	50	3
20190402	4	50	3
20190403	22	40	4
20190403	22	50	5

MySQLだと8.0以降のver.でWindow関数が実装されたようですね。
Window関数を使っているため実行速度は担保されないものの、今までのJOINクエリよりは良さそうです。

まとめ

SQLだと細かな作業が引っかかったりします。
自分以外にも困っている方がいるかもしれないので、よろしければ参考にしてください。
あと、もっと上手い方法があったら切実に教えてください...。

動的なプレースホルダの生成方法ってどう書くんだっけなと毎回ググってる気がする。。

やりたいこと

あるデータ配列を元にプレースホルダーSQL文を作成したい

結果

$sql = 'WHERE column IN (' . implode(',', array_fill(0, count($data), '?')) . ')';

前書

prepared statementなのに値によってsyntax errorが出て混乱したのでメモ

現象

全く関係ない二列に同じ値を設定するとDuplicate column nameが発生する

-- 実行出来る
select
    'abc'
    , 'abc';

-- 実行出来る
select
    *
from
    (
        select
            'abc'
            , 'xyz'
    ) as temp;

-- 実行出来ない
select
    *
from
    (
        select
            'abc'
            , 'abc'
    ) as temp;

※リテラル部分は実際にはprepared statementから指定して実行した

対処法

ASを使用し列名を設定する
dual表を使用する

-- 実行出来る
select
    *
from
    (
        select
            'abc' as c1
            , 'abc' as c2
    ) as temp;

-- 多分これが一番正しい
select
    'abc'
    , 'abc'
from
    dual;

-- 次点
select
    'abc'
    , 'abc'
from
    (
        select
            1
    ) as temp;

※リテラル部分は実際にはprepared statementから指定して実行した

因みに

なんでこんな構成が出現したのかというと、insert into valuesにwhere条件を付けたくてinsert into selectに変換
その際from句が必要になったため
MySQLにdualがある(互換性のためらしい)ことを知っていればこんなことには

前書

prepared statementなのに値によってsyntax errorが出て混乱したのでメモ

現象

全く関係ない二列に同じ値を設定するとDuplicate column nameが発生する

対処法

ASを使用し列名を設定する
dual表を使用する

-- 実行出来る
select
    'abc'
    , 'abc';

-- 実行出来る
select
    *
from
    (
        select
            'abc'
            , 'xyz'
    ) as temp;

-- 実行出来ない
select
    *
from
    (
        select
            'abc'
            , 'abc'
    ) as temp;

-- 実行出来る
select
    *
from
    (
        select
            'abc' as c1
            , 'abc' as c2
    ) as temp;

-- 多分これが一番正しい
select
    'abc'
    , 'abc'
from
    dual;

-- 次点
select
    'abc'
    , 'abc'
from
    (
        select
            1
    ) as temp;

※リテラル部分は実際にはprepared statementから指定して実行した

因みに

なんでこんな構成が出現したのかというと、insert into valuesにwhere条件を付けたくてinsert into selectに変換
その際from句が必要になったため
MySQLにdualがある(互換性のためらしい)ことを知っていればこんなことには