Redash × BigQueryのデータ抽出・分析で使えるtips

# はじめに
こんにちは、ランサーズでエンジニアとしてガンガン機能改善をしている高橋です。
この記事は [Lancers Advent Calendar 2022](https://qiita.com/advent-calendar/2022/lancers) の15日目の記事です。

普段業務の中でredashを利用したデータの抽出の依頼を受けることがあるのですが、実際に利用したbigqueryの関数やredashの機能の中からよりおすすめのものを厳選して紹介します。

具体的な用途や例を提示し、できるだけコピペで利用できるようにしました。


## redash編

### 1. 実行結果にリンクをつける
実行結果にリンクをつけることができます。
```sql
SELECT
    '<a target="_blank" href="https://www.lancers.jp">https://www.lancers.jp</a>' AS URL,
```
![スクリーンショット 2022-12-14 0.13.47.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1298343/1ee1f4ad-5229-68be-8454-b9e7293b25b2.png)

抽出したデータと文字列を結合して使うことも可能です。
```sql
SELECT
    '<a target="_blank" href="https://www.lancers.jp/animals/' || animals.id || '">https://www.lancers.jp/animals/' || animals.id || '</a>' AS URL,
FROM
    animals
```
![スクリーンショット 2022-12-14 0.28.22.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1298343/9fc5b4ab-434a-49f0-82ce-828f0862b54e.png)


### 2. 実行結果の表示にstyleをつける
リンクをつける延長です。様々なhtmlタグやstyleを書くことができます。

使用用途は
- 閾値以上の時背景色を赤にする
- 他の方が実行結果のみをパッとみてわかりやすくするために画像を添える
- アーニャを回転させる

など可能性は広いと思います。おすすめの使い方があったら教えていただけると嬉しいです。

#### divタグをつける
背景色を猫の数によって変えるクエリ
classでスタイルをあてています。
```sql
    CASE
        WHEN cat.color IN ('short_hair','semi_short_hair')
             AND cat_count > 1000 THEN '<div class="bg-success p-10 text-center">' || cat.count || '</div>'
             OR  cat_count > 200  THEN '<div class="bg-warning p-10 text-center">' || cat.count || '</div>'
        ELSE                           '<div class="bg-danger p-10 text-center">'  || cat.count || '</div>'
    END AS cat_count
```
見え方の例：
![スクリーンショット 2022-12-14 1.00.52.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1298343/1a58e1ee-07c5-01bd-66e2-511fb35eef78.png)

利用できるclassはこちらに載っています。
参考:[Conditional Formatting & General Text Formatting](https://discuss.redash.io/t/conditional-formatting-general-text-formatting/1706)

#### アーニャを回転させる
imgタグをつけたりstyleつけたりなんやかんやする(結構なんでもいけた)
```sql
select
'<div style="animation:r5 3s linear infinite;transform-origin: left top;"><img src="https://c.tenor.com/jiRSjXlEuF0AAAAd/waku-waku-excited.gif" width=200px height=200px></div><style>@keyframes r5{0%{ transform:rotateX(0);}100%{ transform:rotateX(360deg);}}</style>' as anya_jouge,
'<div style="animation:3s linear infinite rotation1;"><img src="https://c.tenor.com/jiRSjXlEuF0AAAAd/waku-waku-excited.gif" width=200px height=200px></div><style>@keyframes rotation1{0%{ transform:rotate(0);}100%{ transform:rotate(360deg);}}</style>'as anya_guruguru,
```

![anyas_ugoku.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1298343/285700da-6f5d-24cf-9384-18f0ed311170.gif)


### 3. 検索結果にプルダウン選択できるフィルターをかける 
selectのエイリアスの末に__filterまたは__multiFilterをつけます。
クエリパラメーターとは異なり、実行し直す必要がありません。
```sql
SELECT categories AS "category__filter"
```

![multifilter.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1298343/a9fa14ef-0991-fe04-55c6-f787f566fcf8.gif)

参考：[Query Filters](https://redash.io/help/user-guide/querying/query-filters)

### 4. よく使うクエリをクエリスニペットに登録する
クエリスニペットはクエリのテンプレート機能です

実際の動作
![query_snippet](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1298343/ee2c904a-8ebb-bc12-6795-95c0037efbb6.gif)

- 設定方法
左下のSettingアイコンをクリック > Query Snippetsタブを選択 > New Query Snippetをクリックする
![スクリーンショット 2022-12-14 17.29.28.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1298343/46756258-c405-a7f4-a672-338d94edb4c0.png)





- Trigger
     実際にクエリを作成した際、Trigger部分一致すると候補として表示される('_'から始まる名前にすると他の候補と混ざらず良いかもしれない)

- Description
    スニペットの説明

- Snippet
     スニペットとして展開されるクエリ

[追記]
Triggerを入力してもスニペットが表示されない場合はctrl+spaceを押してからTriggerを入力してください。

## BigQuery編
### 1. PIVOT
PIVOTとは、行を列に変換する演算子です。

具体例として下図に示すと、上のデータから下のものに変換することができます。（[PIVOT 演算子](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax?hl=ja#pivot_operator)より引用）

```sql:概念図
-- Before PIVOT is used to rotate sales and quarter into Q1, Q2, Q3, Q4 columns:
+---------+-------+---------+------+
| product | sales | quarter | year |
+---------+-------+---------+------|
| Kale    | 51    | Q1      | 2020 |
| Kale    | 23    | Q2      | 2020 |
| Kale    | 45    | Q3      | 2020 |
| Kale    | 3     | Q4      | 2020 |
| Kale    | 70    | Q1      | 2021 |
| Kale    | 85    | Q2      | 2021 |
| Apple   | 77    | Q1      | 2020 |
| Apple   | 0     | Q2      | 2020 |
| Apple   | 1     | Q1      | 2021 |
+---------+-------+---------+------+

-- After PIVOT is used to rotate sales and quarter into Q1, Q2, Q3, Q4 columns:
+---------+------+----+------+------+------+
| product | year | Q1 | Q2   | Q3   | Q4   |
+---------+------+----+------+------+------+
| Apple   | 2020 | 77 | 0    | NULL | NULL |
| Apple   | 2021 | 1  | NULL | NULL | NULL |
| Kale    | 2020 | 51 | 23   | 45   | 3    |
| Kale    | 2021 | 70 | 85   | NULL | NULL |
+---------+------+----+------+------+------+
```

```sql:クエリ
SELECT 
    product, 
    Q1, 
    Q2, 
    Q3, 
    Q4
FROM (SELECT product, sales, quarter FROM Produce)
PIVOT(
    SUM(sales) 
    FOR quarter 
    IN ('Q1', 'Q2', 'Q3', 'Q4')
);
```
参考：[PIVOT 演算子](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax?hl=ja#pivot_operator)



### 2. 型変換
CAST関数を使います。[型一覧](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types)はこちら。
```sql
-- CAST(変換したいデータ AS 型名)
CAST(product.created AS STRING)
```


### 3. 月ごとや週ごとで集計する
DATE_TRUNCを使います。
```sql
-- date_partの粒度まで date値を切り詰めます。
DATE_TRUNC(date値, date_part)
```

実際の利用例: オファーの数を月ごとに集計して表示するクエリ
```sql
select
    DATE_TRUNC(offers.created, DAY) as month,
    count(offers.id) as count
from 
    offers
group by
    month
```

### 4. 値がNULLの時に別のカラムの値にする
COALESCE関数を使います。
引数の左から見て最初のnullでない値になります。
```sql
SELECT COALESCE(table.col0, table.col1, 'hogehoge') as result
```

### 5. 順位（ランキング）を取得する
RANK関数を使います。

```sql
SELECT 
    score,
    RANK() OVER (ORDER BY score ASC) AS rank
FROM Numbers
```

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1298343/f9794e97-0b53-d1ad-99ec-08893d164993.png" width="42%">

# 終わりに
便利なもの・利用頻度が比較的高いものを集めました。
今後更新していこうと思いますので、もしおすすめのものがありましたら教えていただけると嬉しいです。

明日のLancers Advent Calendar 2022の担当はつよつよエンジニアの日吉さんです。
アーニャもワクワクですね。