はじめに

こんにちは、ランサーズでエンジニアとしてガンガン機能改善をしている高橋です。
この記事は Lancers Advent Calendar 2022 の15日目の記事です。

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

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

redash編

1. 実行結果にリンクをつける

実行結果にリンクをつけることができます。

SELECT
    '<a target="_blank" href="https://www.lancers.jp">https://www.lancers.jp</a>' AS URL,

抽出したデータと文字列を結合して使うことも可能です。

SELECT
    '<a target="_blank" href="https://www.lancers.jp/animals/' || animals.id || '">https://www.lancers.jp/animals/' || animals.id || '</a>' AS URL,
FROM
    animals

2. 実行結果の表示にstyleをつける

リンクをつける延長です。様々なhtmlタグやstyleを書くことができます。

使用用途は

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

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

divタグをつける

背景色を猫の数によって変えるクエリ
classでスタイルをあてています。

    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

見え方の例：

利用できるclassはこちらに載っています。
参考:Conditional Formatting & General Text Formatting

アーニャを回転させる

imgタグをつけたりstyleつけたりなんやかんやする(結構なんでもいけた)

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,

3. 検索結果にプルダウン選択できるフィルターをかける

selectのエイリアスの末に__filterまたは__multiFilterをつけます。
クエリパラメーターとは異なり、実行し直す必要がありません。

SELECT categories AS "category__filter"

参考：Query Filters

4. よく使うクエリをクエリスニペットに登録する

クエリスニペットはクエリのテンプレート機能です

実際の動作

設定方法
左下のSettingアイコンをクリック > Query Snippetsタブを選択 > New Query Snippetをクリックする
Trigger
実際にクエリを作成した際、Trigger部分一致すると候補として表示される('_'から始まる名前にすると他の候補と混ざらず良いかもしれない)
Description
スニペットの説明
Snippet
スニペットとして展開されるクエリ

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

BigQuery編

1. PIVOT

PIVOTとは、行を列に変換する演算子です。

具体例として下図に示すと、上のデータから下のものに変換することができます。（PIVOT 演算子より引用）

概念図

-- 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 |
+---------+------+----+------+------+------+

クエリ

SELECT 
    product, 
    Q1, 
    Q2, 
    Q3, 
    Q4
FROM (SELECT product, sales, quarter FROM Produce)
PIVOT(
    SUM(sales) 
    FOR quarter 
    IN ('Q1', 'Q2', 'Q3', 'Q4')
);

参考：PIVOT 演算子

2. 型変換

CAST関数を使います。型一覧はこちら。

-- CAST(変換したいデータ AS 型名)
CAST(product.created AS STRING)

3. 月ごとや週ごとで集計する

DATE_TRUNCを使います。

-- date_partの粒度まで date値を切り詰めます。
DATE_TRUNC(date値, date_part)

実際の利用例: オファーの数を月ごとに集計して表示するクエリ

select
    DATE_TRUNC(offers.created, DAY) as month,
    count(offers.id) as count
from 
    offers
group by
    month

4. 値がNULLの時に別のカラムの値にする

COALESCE関数を使います。
引数の左から見て最初のnullでない値になります。

SELECT COALESCE(table.col0, table.col1, 'hogehoge') as result

5. 順位（ランキング）を取得する

RANK関数を使います。

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

終わりに

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

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