NotebooksでShopifyQLを使用する

ShopifyQLとは、コマース向けに構築されたShopifyのクエリ言語です。クエリ言語は、データをデータベースにリクエストして取得するために使用します。ShopifyのNotebooksアプリでShopifyQLを使用すれば、自身のビジネスのデータベースを検索してデータを取得し、ビジネスへの理解を深めることができます。

ShopifyQL Notebooksクエリで使用できる値と寸法のリスト一覧については、「注文スキーマ」および「商品スキーマ」を参照してください。

ShopifyQLの概要

ストアのデータは、定義された列と行で構造化されたデータベーステーブルに格納されます。列では、売上など格納している情報のタイプを定義し、行では、売上2,450米ドルなど、データタイプの実際の値を指定します。

データを有意味な形式で取得するには、クエリをデータベースに送信する必要があります。クエリとは、特定のデータを回答として求める質問のことです。ShopifyQLのようなクエリ言語は、その質問を構築する規格化された方法です。クエリは、キーワードとその対応するパラメーターで構成されています。特定のパラメーターを伴うさまざまなキーワードの組み合わせでクエリは構築されます。クエリを構築したら、実行して回答を受け取ることができます。

次に示すのは、クエリとして記述したShopifyQL構文の例です。キーワードは太字になっており、対応するパラメーターは括弧で囲まれています。ここに示されているパラメーターはプレースホルダーです。

FROM { table_name }
  SHOW { column1, column2, ... }
  GROUP BY { dimension | date_group }
  WHERE { condition }
  SINCE { date_offset }
  UNTIL { date_offset }
  ORDER BY { column } DESC
  LIMIT { count }

用語集

ShopifyQLの用語の定義

キーワード	定義
寸法	データを分割して分類し、明確に表示できるようにする属性。ディメンションの一般的な例としては、時間、商品、場所などがあります。ディメンションは、ShopifyQLでパラメーターとして使用されます。
キーワード	クエリを指示するコマンドとして機能するShopifyQL構文。
指標	データの定量的測定。指標の一般的な例には、販売合計、注文数、売上総利益が含まれます。
パラメーター	クエリに含めるデータベースの要素や仕様を識別するShopifyQL構文。

キーワードの参照表

次に示すのは、クエリを記述する際に使用できるキーワードです。

ShopifyQLキーワードのリスト

キーワード	機能の用途
FROM	データを選択するデータセットテーブルを指定します。
SHOW	データセットテーブルから抽出する列を選択します。
VISUALIZE	折れ線グラフや棒グラフで視覚化したデータを表示します。
GROUP BY	抽出されたデータをディメンションまたは時間ディメンションでグループ化します。
WHERE	選択する行が満たす必要のある条件を定義します。
SINCE	過去の指定した時間からのデータを表示します。
UNTIL	過去の指定された時間までのデータを表示します。
DURING	過去の指定した時間からのデータを表示します。
COMPARE TO	過去の指定された時間までのデータを表示します。
ORDER BY	データを配列する基準にする列を指定します。
LIMIT	表示されるデータの行数を制限します。
AS	列の名前を選択した名前に変更します。

ShopifyQLクエリを記述する

ShopifyQLクエリでは、高レベルのデータインサイトが得られる基本的なクエリから、詳細なインサイトが得られる包括的なクエリまでを利用できます。各キーワードには、クエリを構築する特定の機能があります。

ShopifyQLの基本：FROMとSHOW

最も簡単なShopifyQLクエリは、FROMとSHOWの順序で記述する2つのキーワードだけで作成できます。後ろにテーブル名パラメーターが続くFROMを使用すると、クエリを実行するテーブルが指定されます。後ろに列名パラメーターが続くSHOWを使用すると、選択する列が指定されます。

たとえば、次のクエリを記述すると、販売テーブルからすべての商品のIDと対応するタイトルを表示できます。

FROM products
  SHOW product_id, product_title

---

データのグループ化：GROUP BY

地域別のグループ分けした販売高などのように、指標をディメンション別に分割化する場合、GROUP BYキーワードを使用します。GROUP BYキーワードは、どのディメンションパラメーターとも組み合わせることができます。

たとえば、請求先の国や地域で合計配送料金を分類するクエリは、次のように記述します。

FROM orders
  SHOW shipping
  GROUP BY billing_country, billing_region

次の記述は、時間ディメンションを使用して月別の純売上高を表示するクエリの例です。

FROM orders
  SHOW net_sales
  GROUP BY month

上記のクエリを送信しても、販売がない月からは結果が返されません。連続した完全な期間の結果を返すクエリが必要な場合は、次のようにALL修飾子を使用します。

FROM orders
  SHOW net_sales
  GROUP BY month ALL
  SINCE last_year
  UNTIL today

ALL修飾子を使用する場合、SINCEとUNTILも指定する必要があります。ALL修飾子は時間ディメンションでのみ動作することに注意してください。

時間ディメンション

次に示すのは、データをグループ化するために使用できる時間ディメンションです。

ShopifyQLの時間ディメンション

演算子	機能の用途
時間	暦日の時間単位でグループ化します。
日	暦日でグループ化します。
週	暦週でグループ化します。
月	暦月でグループ化します。
quarter	四半期でグループ化します。
年	暦年でグループ化します。
hour_of_day	24時間単位の時間でグループ化します (1、2、...、24)。
day_of_week	day of weekでグループ化します (M,T,W,...,S)。
week_of_year	年の週番号でグループ化します (1、2、...、52)。

---

データの絞り込み：WHERE

WHEREキーワードを使用すると、ShopifyQLクエリ全体をディメンションで絞り込むことができます。

たとえば、特定の地域のみについて月別にグループ化して純売上高を返してもらう場合、クエリは次のようになります。

FROM orders
  SHOW net_sales
  GROUP BY month ALL
  WHERE billing_region = 'ohio'
  SINCE last_year
  UNTIL today

上記の例で示されているように、SHOWやGROUP BYのキーワードにWHEREのパラメーターが含まれていない場合でも、このパラメーターで結果セットを絞り込むことができます。この場合、結果セットにbilling_regionが含まれていなくても、オハイオ州のみに請求先住所がある注文の純売上高は、すべての月で絞り込まれます。

比較演算子

WHEREキーワードでは、比較演算子を使用してデータを絞り込みます。上記の例では「=」を使用して、クエリの結果を特定の値で絞り込むように指定していましたが、ほかにも次に示す演算子を利用できます。

ShopifyQLの比較演算子

比較演算子	機能の用途
=	一致
!=	一致しない
<	より小さい
>	より大きい
<=	以下
>=	以上

論理演算子

データをさらに絞り込むには、クエリに論理演算子を追加します。次に示すのは、ShopifyQLの論理演算子です。

ShopifyQLの論理演算子

論理演算子	機能の用途
と	ANDで区切られた条件を満たしたすべての行を表示するために絞り込みます。
OR	ORで区切られたいずれかの条件を満たしたすべての行を表示するために絞り込みます。
NOT	指定された値を含まない行など、条件を満たしていない行のみを表示するために絞り込みます。

論理演算子を追加すると、WHEREキーワードで複数の絞り込みを使用することができます。

データセットクエリの例に加えて、請求先住所がオハイオ州にあり、ディスカウントが適用されたすべての注文の純売上高を月ごとにグループ化するには、次のクエリを実行します。

FROM orders
  SHOW net_sales
  GROUP BY month ALL
  WHERE billing_region = 'ohio' AND discounts > 0
  SINCE last_year
  UNTIL today

---

日付範囲：SINCEとUNTIL

クエリを日付または一定の期間で絞り込む場合、SINCEおよびUNTILのキーワードと関連するパラメーターを使用することができます。このように期間のみを絞り込むキーワードはほかにありません。

たとえば、次のクエリでは、今日を起点としてカナダでの過去12か月間の純売上高を取得します。

FROM orders
  SHOW net_sales
  GROUP BY month ALL
  WHERE billing_country = 'Canada'
  SINCE -12m
  UNTIL today

オフセット演算子

特定の日付または日付オフセットで絞り込むことができます。次に示すのは、ShopifyQLのオフセット演算子です。

ShopifyQLのオフセット演算子

オフセット演算子	機能の用途
-{#}d	クエリを実行した日からさかのぼった日数。
-{#}w	クエリを実行した日からさかのぼった週数。
-{#}m	クエリを実行した日からさかのぼった月数。
-{#}q	クエリを実行した日からさかのぼった四半期数。
-{#}y	クエリを実行した日からさかのぼった年数。
yyyy-mm-dd	特定の日付。
今日	クエリを実行した日付。
yesterday	クエリを実行した時を起点とした過去24時間。

---

日付範囲で絞り込む：DURING

DURINGキーワードを使用すると、日付範囲での日付絞り込みが簡単になります。DURINGキーワードを使用して、暦年や特定の月などの既知の期間、またはブラックフライデー・サイバーマンデーなどの毎年異なる日程の日付範囲でクエリ結果を絞り込むことができます。以下の例をご覧ください。

FROM orders
  SHOW sum(net_sales)
  GROUP BY day ALL
  DURING bfcm2021

DURINGでは、次の特定の日付範囲を指定する演算子を使用できます。

ShopifyQLの特定の日付範囲を指定する演算子

日付範囲を指定する演算子	機能の用途
今日	クエリを実行した日付。
yesterday	クエリを実行した時を起点とした過去24時間。
this_week	現在の暦週。
this_month	現在の暦月。
this_quarter	現在の暦四半期。
this_year	現在の暦年
last_week	前の暦週。
last_month	前の暦月。
last_quarter	前の暦四半期。
last_year	前の暦年
bfcm2022	2022年11月25日～11月28日。
bfcm2021	2021年11月26日〜11月29日。

---

日付範囲の間で比較する：COMPARE TO

COMPARE TOキーワードを使用すると、DURINGの日付範囲とCOMPARE TOの日付範囲との間でデータを比較できます。COMPARE TOでは、DURINGセクションに列挙されている特定の日付範囲を指定する演算子をどれでも使用できます。COMPARE TOに使用する演算子は、DURINGに使用する演算子と同じ長さの期間である必要があります。たとえば、DURING this_week COMPARE TO last_weekは有効な組み合わせですが、DURING this_week COMPARE TO last_monthはそうではありません。

次の例では、2022年のブラックフライデー・サイバーマンデーと2021年のブラックフライデー・サイバーマンデーの期間の純売上高を比較しています。

FROM orders
  SHOW sum(net_sales)
  GROUP BY day ALL
  DURING bfcm2022
  COMPARE TO bfcm2021

---

データの並べ替え：ORDER BY

ORDER BYキーワード、並びにそのパラメーターである昇順を表すASCや降順を表すDESCを使用すると、クエリから返されたデータを並び替える方法を指定できます。

クエリに含める指標やディメンション (複数のフィールドなど) は、ORDER BYキーワードで指定できます。

たとえば、次のクエリでは、請求先の国や地域ごとの純売上高を、請求先の国、次に各国内の請求先の地域で逆アルファベット順に並べ替えて返します。

FROM orders
  SHOW net_sales
  GROUP BY billing_country, billing_region
  SINCE -1y
  UNTIL today
  ORDER BY billing_country, billing_region DESC

指標やディメンションを記述する順序は重要です。ORDER BYに複数の値を指定すると、その並び替えは記述した順序で指標またはディメンションごとに適用されます。

---

制限

LIMITキーワードを使用すると、クエリが返す行数を指定できます。これは、各列のデータの概観を単に把握したい場合に役立ちます。あるいは、ORDER BYと組み合わせて上位のリストと下位のリストを作成することができます。

次の例では、LIMITとORDER BYを使用して、過去3か月間で、数量別の売上上位10商品のリストを作成します。

FROM products
  SHOW net_product_quantity
  GROUP BY product_title
  SINCE -3m
  UNTIL today
  ORDER BY net_product_quantity DESC
  LIMIT 10

---

クエリをグラフで表す: VISUALIZEとTYPE

VISUALIZEキーワードを使用すると、折れ線グラフや棒グラフでデータを可視化するShopifyQLクエリを記述できます。

TYPEキーワードの追加は任意ですが、クエリが折れ線グラフまたは棒グラフで可視化されたデータを返すためには、キーワードに「線」または「棒」を含める必要があります。TYPEがクエリに含まれていない場合、ShopifyQLでは、クエリに最適な方法でデータを可視化するよう自動的に決定します。クエリで記述通りに可視化できない場合、ShopifyQLでは表形式のデータを返します。

たとえば、動きを示す折れ線グラフを使用すると、昨年1年間の販売傾向を月別に可視化できます。次に示すクエリでは、昨年の月別の総売上高を示す時系列グラフを返します。総売上高は単線で表示され、x軸は月を、y軸は総売上高を表します。

FROM orders
  VISUALIZE gross_sales
  TYPE line
  GROUP BY month ALL
  SINCE -1y
  UNTIL today

---

算術演算子

ShopifyQLを使用すると、データにある指標を使用して算術演算を実行できます。次の算術演算子を利用できます。

ShopifyQLのオフセット演算子

数学演算子	機能の用途
+	2つの数値の加算。
-	2つの数値の減算。
*	2つの数値の乗算。
/	2つの数値の除算。

たとえば、次に示すクエリでは、昨年の地域ごとの注文金額を算出します。指標で算術演算子を使用する際は、ASキーワードを使用して新しい指標に新しい名前を割り当てることができます。

FROM orders
  SHOW (net_sales + returns) AS order_value, orders, (net_sales + returns)/orders AS sales_per_order
  GROUP BY billing_region
  SINCE -1y
  UNTIL today

---

関数

ShopifyQL関数を使用すると、Microsoft Excelのピボットテーブルに類似した機能で列を集計できます。列を集計すると、列が組み合わされて新しい値が作成されます。次の関数演算子は、最新バージョンのShopifyQLで利用できます。

ShopifyQLのオフセット演算子

関数演算子	機能の用途
Count()	結果セットのインスタンス数。
sum()	結果セットの値の合計。
min()	結果セットの最小値。
Max()	結果セットの最大値。
avg()	結果セットの平均値。

sum、min、max、およびavgの関数は数値でのみ使用できます。countはディメンション属性のさまざまなインスタンスをカウントするために使用できます。関数の引数として集計されたフィールドを使用することはできません。集計されたフィールドの末尾は_sum、_count、または_percentです。

たとえば、次のクエリでは販売_合計がすでに集計されているため、エラーを返します。

FROM orders
  SHOW sum(total_sales)

次に示すのは、集計されたフィールドと集計関数を組み合わせた有効なクエリです。

FROM orders
  SHOW average_order_value, sum(gross_sales)
  GROUP BY billing_region
  SINCE 2021-01-01
  UNTIL 2021-12-31

このクエリでは、sum関数の結果として、平均注文金額の合計、総売上高の合計を集計して返します。これらの指標は、2021年に行われた注文すべてを請求先の地域別に分類したものです。

---

コメント

コメントを使用すると、ShopifyQL文の各セクションについて説明したり、ShopifyQL文が実行されないようにしたりすることができます。コメント内のテキストは、実行時間中は無視されます。

単一行のコメントは--で始まり、その行の末尾で終わります。

複数行のコメントは/*で始まり、*/で終わります。

FROM orders
  SHOW average_order_value, sum(gross_sales)
  -- the line below has been commented out and will not run
  -- GROUP BY billing_region
  WHERE billing_country = 'United States'
  /*
  this line and the two lines below it have been commented out and will not run
  SINCE 2021-01-01
  UNTIL 2021-12-31
*/