各種データベースの乱数生成

Oracle
DBMS_RANDOM.VALUE は、０から１の数を生成し、精度は38桁

SELECT  DBMS_RANDOM.VALUE FROM dual

は、0.73988067528604570993642928024798559837 を生成したりする。

整数 1～1000 の範囲で欲しい時は

SELECT  TRUNC(DBMS_RANDOM.VALUE(1,1000)) FROM dual

整数 -1000～0 の範囲で欲しい時は

SELECT  TRUNC(DBMS_RANDOM.VALUE(-1000,0))  FROM dual

PostgreSQL
random() 関数を使う。

SELECT  random()

random() も、０から１の数を生成し、PostgreSQL version 15 では、
0.17280202311237436 の様になる。小数点桁数＝17
整数範囲指定して整数で欲しい時は、
TRUNC((random() * (最小値 - 最大値)） + 最大値
という書式に従って、
整数 1～1000 の範囲で欲しい時は、

SELECT  TRUNC(random() * (1 - 1000)) + 1000

整数 -1000～0 の範囲で欲しい時は

SELECT  TRUNC(random() * (-1000 - 0))

MySQL 8.0
RAND() 関数を使う。
RAND() も、０から１の数を生成し、

SELECT RAND()

結果は、0.1396465479765521 のように、小数点桁数＝16
整数 1～1000 の範囲で欲しい時は、

SELECT TRUNCATE(RAND()*1000,0) + 1

整数 -1000～0 の範囲で欲しい時は

SELECT (TRUNCATE(RAND()*1000,0) + 1) * -1

INTERSECT の使い道

SQLの INTERSECT の使い道って何だろう？と。。。
よくあるテーブルの抽出結果を任意のテーブルに insert するのは、

INSERT INTO tableA(
     column1
    ,column2
)
SELECT column1
      ,column2
FROM  tableB

登録で重複が起きる時、UPSERT文や以下のようなMERGE文、
DBの種類によって書き方に多少の違いがあるにせよだいたい以下のようにして実行しているが、

MERGE INTO tableA
USING  tableB
ON 結合条件
WHEN MATCHED THEN
    update文
WHEN NOT MATCHED THEN
    insert文

を使うであろう。
これを実行する前に、重複だけを抽出して欲しいという要求がきたらどうするか？
INTERSECT を使う。そもそもINTERSECT：積集合は、２つの検索結果で結果として
同じであるものを求めることであり、集合の重なり部分を求めること。
だから、

SELECT column1
      ,column2
FROM  tableA
INTERSECT
SELECT column1
      ,column2
FROM  tableB

のように、UNION や UNION ALL を書いた時のように使用する。
UNION のように列名も同じである必要がある。

意外と大量に重複だけを求めて欲しいという要件があまりないので実践でも少ないのかもしれない。

DBeaver の接続設定情報、ユーザー名とパスワードを参照する

DBクライアントツールとして皆大好き、DBeaver を使っていて設定したユーザ、パスワードを忘れてしまい
暗号化されたパスワードを参照したい時、どうしようと困ったことがある。
暗号のキーを知っていれば復号できる。以下、DBeaver内部にコードから知ることができて、
https://github.com/dbeaver/dbeaver/blob/57cec8ddfdbbf311261ebd0c7f957fdcd80a085f/plugins/org.jkiss.dbeaver.model/src/org/jkiss/dbeaver/model/impl/app/DefaultSecureStorage.java#L31
どうやら、AES暗号、パディングは、PKCS5 みたいなので、
暗号化されたファイルのパスを与えて読み込ませて復号する処理のクラスを用意した。

DBeaver設定の接続情報ユーザ、パスワードを暗号化したファイルは、Windows のユーザディレクトリの以下の場所に、
　　C:\Users\${userName}\AppData\Roaming\DBeaverData\workspace6\General\.dbeaver
　　の下、
credentials-config.json　が作成されているのでこれをこのクラスで処理する。

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.security.InvalidAlgorithmParameterException;
import java.security.InvalidKeyException;
import java.security.NoSuchAlgorithmException;
import java.util.Map;
import java.util.Scanner;
import javax.crypto.Cipher;
import javax.crypto.CipherInputStream;
import javax.crypto.NoSuchPaddingException;
import javax.crypto.SecretKey;
import javax.crypto.spec.IvParameterSpec;
import javax.crypto.spec.SecretKeySpec;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
/**
 * DBeaver credentials-config.json の解析 
 */
public class DbeaverParse{
    private final byte[] LOCAL_KEY_CACHE 
    = new byte[]{ -70, -69, 74, -97, 119, 74, -72, 83, -55, 108, 45, 101, 61, -2, 84, 74 };
    private Path configpath;
    
    public DbeaverParse(String credentialsConfigPath) {
        configpath = Paths.get(credentialsConfigPath);
    }
    
    public String decrypt() throws InvalidAlgorithmParameterException, InvalidKeyException, IOException
    , NoSuchPaddingException, NoSuchAlgorithmException {
        byte[] contents = Files.readAllBytes(configpath);
        try (InputStream byteStream = new ByteArrayInputStream(contents)) {
            byte[] fileIv = new byte[16];
            byteStream.read(fileIv);
            Cipher cipher = Cipher.getInstance("AES/CBC/PKCS5Padding");
            SecretKey aes = new SecretKeySpec(LOCAL_KEY_CACHE, "AES");
            cipher.init(Cipher.DECRYPT_MODE, aes, new IvParameterSpec(fileIv));
            try(CipherInputStream cipherIn = new CipherInputStream(byteStream, cipher);
                 Scanner scanner = new Scanner(cipherIn).useDelimiter("\\A")){
                return scanner.hasNext() ? scanner.next() : "";
            }
        }
    }
    public String prettyJsonString(String str) {
        try{
            ObjectMapper mapper = new ObjectMapper();
            return new ObjectMapper().writerWithDefaultPrettyPrinter()
                    .writeValueAsString(mapper.readValue(str, Map.class));
        }catch(JsonProcessingException e){
            throw new RuntimeException(e.getMessage(), e);
        }
    }
}

復号した結果Json文字列が、フラットで読みにくいかもしれないので、Jackson のJsonライブラリで、
整形できるようにしておく。
Jackson は、Maven で以下のように取得しておくと良いだろう

<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-databind</artifactId>
    <version>2.13.2.1</version>
</dependency>
<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-annotations</artifactId>
    <version>2.13.2</version>
</dependency>
<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-core</artifactId>
    <version>2.13.2</version>
</dependency>
<dependency>
    <groupId>com.fasterxml.jackson.datatype</groupId>
    <artifactId>jackson-datatype-jsr310</artifactId>
    <version>2.13.2</version>
</dependency>

DbeaverParse の 呼び出し

try{
    DbeaverParse dbeaverParse = new DbeaverParse("C:/Users/XXXX/AppData/Roaming/DBeaverData/workspace6/General/.dbeaver/credentials-config.json");
    
    String json = dbeaverParse.decrypt();
    
    String jsonPretty =  dbeaverParse.prettyJsonString(json);

    System.out.println(jsonPretty);

}catch(InvalidKeyException | InvalidAlgorithmParameterException | NoSuchPaddingException | NoSuchAlgorithmException | IOException e){
    e.printStackTrace();
}

おそらくPythonでも書けるだろう。

複数の文字列のsplit ⇒ 行処理するSQL （ORACLE）

先日、Oracleで１つにカンマ区切り文字列を行変換する例を書いた
文字列のsplit ⇒ 行処理するSQL （ORACLE） - Oboe吹きプログラマの黙示録

１つではなく、複数のカンマ区切り文字列から、TABLE を作成する方法は、以下のように、
TABLE(CAST(MULTISET( 区切りレベル CONNECT BY REGEXP_SUBSTR 対象を
各々に適用させれば良い。

例）

WITH prefcture_strings AS (
    SELECT '東京,神奈川,千葉,茨城' AS value FROM dual
), prfctcd_strings AS (
    SELECT '13,14,12,08' AS value FROM dual
), point_strings AS (
    SELECT '100,80,60,55' AS value FROM dual
), splits AS (
     SELECT lines.column_value AS ROWNO
    , TRIM(REGEXP_SUBSTR(prefcture_strings.value,'[^,]+', 1, lines.column_value))  AS PREFECTURE
    , TRIM(REGEXP_SUBSTR(prfctcd_strings.value,'[^,]+', 1, lines.column_value))    AS PRFCTCD
    , TRIM(REGEXP_SUBSTR(point_strings.value,'[^,]+', 1, lines.column_value))      AS POINT
    FROM prefcture_strings, prfctcd_strings, point_strings
        ,TABLE(CAST(MULTISET(SELECT LEVEL FROM dual
CONNECT BY REGEXP_SUBSTR(prefcture_strings.value, '[^,]+', 1, LEVEL) IS NOT NULL) AS sys.odciNumberList
     )) lines
)
SELECT ROWNO
       , PREFECTURE
       , PRFCTCD
       , POINT
FROM splits

FROM句で対象を並べ、REGEXP_SUBSTR で各カンマ区切り文字列 FROM dual を指定する。
splits テーブルSELECT結果は以下のようになる。

ROWNO	PREFECTURE	PRFCTCD	POINT
1	東京	13	100
2	神奈川	14	80
3	千葉	12	60
4	茨城	08	55

区切りの数が全て同じであることが前提である。

UNPIVOTで格納される値を日本語にする【Oracle】

以下のテーブルがある。
JOBROUTE テーブル

id	STATION	CAR	TRAIN	WALK
1	新宿	5	12	120
2	渋谷	7	18	130

このCAR, TRAIN, WALK 列をUNPIVOTする。

SELECT 
     id, STATION, M_TYPE, T_MINUTES
FROM JOBROUTE 
UNPIVOT(T_MINUTES FOR M_TYPE IN(CAR , TRAIN , WALK))

結果

id	STATION	M_TYPE	T_MINUTES
1	新宿	CAR	5
1	新宿	TRAIN	12
1	新宿	WALK	120
2	渋谷	CAR	7
2	渋谷	TRAIN	18
2	渋谷	WALK	130

UNPIVOT で作成される列名を日本語にしたい。
CAR ⇒ 車
TRAIN ⇒ 電車
WALK ⇒ 徒歩
というように。。。

安全で確実な方法は、SELECT句でCASE文を書く方法

SELECT 
      id
     ,STATION
     ,CASE M_TYPE
          WHEN 'CAR' THEN '車'
          WHEN 'TRAIN' THEN '電車'
          WHEN 'WALK' THEN '徒歩'
      END AS M_TYPE
     ,T_MINUTES
FROM JOBROUTE 
UNPIVOT(T_MINUTES FOR M_TYPE IN(CAR , TRAIN , WALK))

SELECTでCASE式を避ける方法、一旦サブクエリになってしまうが、

WITH TJOBROUTE AS (
    SELECT 
      id
     ,STATION
     ,CAR       AS "車"
     ,TRAIN     AS "電車"
     ,WALK      AS "徒歩"
     ,T_MINUTES
FROM JOBROUTE 
)
SELECT 
      id
     ,STATION
     ,M_TYPE
     ,T_MINUTES
FROM TJOBROUTE 
UNPIVOT(T_MINUTES FOR M_TYPE IN("車", "電車", "徒歩"))

ここで、注意しなければならないのは、
Oracle は、AS 句で日本語など２バイト文字を使う場合は、シングルクォートではなく、
ダブルクォーテーションを使うことである。

id	STATION	M_TYPE	T_MINUTES
1	新宿	車	5
1	新宿	電車	12
1	新宿	徒歩	120
2	渋谷	車	7
2	渋谷	電車	18
2	渋谷	徒歩	130

文字列のsplit ⇒ 行処理するSQL （PostgreSQL）

文字列のsplit ⇒ 行処理するSQL （ORACLE） - Oboe吹きプログラマの黙示録
を書いたので、PostgreSQL で同じことをしてみる。

方法は２通り。

string_to_array() を使う。

WITH strings AS (
    SELECT UNNEST(string_to_array('A,B,C', ',')) AS split_value
)
SELECT 
    row_number() OVER () AS position
  , split_value
  , COUNT(*) OVER()      AS splitlength
FROM strings

string_to_table() を使う

WITH strings AS (
    SELECT string_to_table('A,B,C', ',') AS  split_value
)
SELECT 
    row_number() OVER () AS position
  , split_value
  , COUNT(*) OVER()      AS splitlength
FROM strings

結果は、

position	split_value	splitlength
1	A	3
2	B	3
3	C	3

文字列のsplit ⇒ 行処理するSQL （ORACLE）

UNPIVOT に近い、わざわざSQLで行うことがナンセンスな気もしたが、
split 対象の文字列を mybatis などから動的パラメータとして指定して結果を他のテーブルと結合して
処理するＳＱＬなど応用範囲は広がると思った。

Oracle の場合のサンプル

’A,B,C' をカンマ区切りで分割、

WITH strings AS (
    SELECT
        'A,B,C' AS value
    FROM dual
)
, splits AS (
    SELECT
        lines.column_value AS position
        , REGEXP_SUBSTR(strings.value,'[^,]+', 1, lines.column_value) AS split_value
        , COUNT(*) OVER()  AS splitlength
    FROM strings
        ,TABLE(CAST(MULTISET(SELECT LEVEL FROM dual
 CONNECT BY REGEXP_SUBSTR(strings.value, '[^,]+', 1, LEVEL) IS NOT NULL) AS sys.odciNumberList
                   )
              ) lines
)
SELECT * FROM splits

結果、、

position	split_value	splitlength
1	A	3
2	B	3
3	C	3