NotebooksでShopifyQLを使用する
ShopifyQLとは、コマース向けに構築されたShopifyのクエリ言語です。クエリ言語は、データをデータベースにリクエストして取得するために使用します。ShopifyのNotebooksアプリでShopifyQLを使用すれば、自身のビジネスのデータベースを検索してデータを取得し、ビジネスへの理解を深めることができます。
ShopifyQL Notebooksクエリで使用できる値と寸法のリスト一覧については、「注文スキーマ」および「商品スキーマ」を参照してください。
ShopifyQLの概要
ストアのデータは、定義された列と行で構造化されたデータベーステーブルに格納されます。列では、売上など格納している情報のタイプを定義し、行では、売上2,450米ドルなど、データタイプの実際の値を指定します。
データを有意味な形式で取得するには、クエリをデータベースに送信する必要があります。クエリとは、特定のデータを回答として求める質問のことです。ShopifyQLのようなクエリ言語は、その質問を構築する規格化された方法です。クエリは、キーワードとその対応するパラメーターで構成されています。特定のパラメーターを伴うさまざまなキーワードの組み合わせでクエリは構築されます。クエリを構築したら、実行して回答を受け取ることができます。
次に示すのは、クエリとして記述したShopifyQL構文の例です。キーワードは太字になっており、対応するパラメーターは括弧で囲まれています。ここに示されているパラメーターはプレースホルダーです。
FROM{table_name}SHOW{column1,column2,...}GROUPBY{dimension|date_group}WHERE{condition}SINCE{date_offset}UNTIL{date_offset}ORDERBY{column}DESCLIMIT{count}用語集
| キーワード | 定義 |
|---|---|
| 寸法 | データを分割して分類し、明確に表示できるようにする属性。ディメンションの一般的な例としては、時間、商品、場所などがあります。ディメンションは、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と対応するタイトルを表示できます。
FROMproductsSHOWproduct_id,product_titleデータのグループ化：GROUP BY
地域別のグループ分けした販売高などのように、指標をディメンション別に分割化する場合、GROUP BYキーワードを使用します。GROUP BYキーワードは、どのディメンションパラメーターとも組み合わせることができます。
たとえば、請求先の国や地域で合計配送料金を分類するクエリは、次のように記述します。
FROMordersSHOWshippingGROUPBYbilling_country,billing_region次の記述は、時間ディメンションを使用して月別の純売上高を表示するクエリの例です。
FROMordersSHOWnet_salesGROUPBYmonth上記のクエリを送信しても、販売がない月からは結果が返されません。連続した完全な期間の結果を返すクエリが必要な場合は、次のようにALL修飾子を使用します。
FROMordersSHOWnet_salesGROUPBYmonthALLSINCElast_yearUNTILtodayALL修飾子を使用する場合、SINCEとUNTILも指定する必要があります。ALL修飾子は時間ディメンションでのみ動作することに注意してください。
時間ディメンション
次に示すのは、データをグループ化するために使用できる時間ディメンションです。
| 演算子 | 機能の用途 |
|---|---|
| 時間 | 暦日の時間単位でグループ化します。 |
| 日 | 暦日でグループ化します。 |
| 週 | 暦週でグループ化します。 |
| 月 | 暦月でグループ化します。 |
| 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クエリ全体をディメンションで絞り込むことができます。
たとえば、特定の地域のみについて月別にグループ化して純売上高を返してもらう場合、クエリは次のようになります。
FROMordersSHOWnet_salesGROUPBYmonthALLWHEREbilling_region='ohio'SINCElast_yearUNTILtoday上記の例で示されているように、SHOWやGROUP BYのキーワードにWHEREのパラメーターが含まれていない場合でも、このパラメーターで結果セットを絞り込むことができます。この場合、結果セットにbilling_regionが含まれていなくても、オハイオ州のみに請求先住所がある注文の純売上高は、すべての月で絞り込まれます。
比較演算子
WHEREキーワードでは、比較演算子を使用してデータを絞り込みます。上記の例では「=」を使用して、クエリの結果を特定の値で絞り込むように指定していましたが、ほかにも次に示す演算子を利用できます。
| 比較演算子 | 機能の用途 |
|---|---|
| = | 一致 |
| != | 一致しない |
| < | より小さい |
| > | より大きい |
| <= | 以下 |
| >= | 以上 |
論理演算子
データをさらに絞り込むには、クエリに論理演算子を追加します。次に示すのは、ShopifyQLの論理演算子です。
| 論理演算子 | 機能の用途 |
|---|---|
| と | ANDで区切られた条件を満たしたすべての行を表示するために絞り込みます。 |
| OR | ORで区切られたいずれかの条件を満たしたすべての行を表示するために絞り込みます。 |
| NOT | 指定された値を含まない行など、条件を満たしていない行のみを表示するために絞り込みます。 |
論理演算子を追加すると、WHEREキーワードで複数の絞り込みを使用することができます。
データセットクエリの例に加えて、請求先住所がオハイオ州にあり、ディスカウントが適用されたすべての注文の純売上高を月ごとにグループ化するには、次のクエリを実行します。
FROMordersSHOWnet_salesGROUPBYmonthALLWHEREbilling_region='ohio'ANDdiscounts>0SINCElast_yearUNTILtoday日付範囲：SINCEとUNTIL
クエリを日付または一定の期間で絞り込む場合、SINCEおよびUNTILのキーワードと関連するパラメーターを使用することができます。このように期間のみを絞り込むキーワードはほかにありません。
たとえば、次のクエリでは、今日を起点としてカナダでの過去12か月間の純売上高を取得します。
FROMordersSHOWnet_salesGROUPBYmonthALLWHEREbilling_country='Canada'SINCE-12mUNTILtodayオフセット演算子
特定の日付または日付オフセットで絞り込むことができます。次に示すのは、ShopifyQLのオフセット演算子です。
| オフセット演算子 | 機能の用途 |
|---|---|
| -{#}d | クエリを実行した日からさかのぼった日数。 |
| -{#}w | クエリを実行した日からさかのぼった週数。 |
| -{#}m | クエリを実行した日からさかのぼった月数。 |
| -{#}q | クエリを実行した日からさかのぼった四半期数。 |
| -{#}y | クエリを実行した日からさかのぼった年数。 |
| yyyy-mm-dd | 特定の日付。 |
| 今日 | クエリを実行した日付。 |
| yesterday | クエリを実行した時を起点とした過去24時間。 |
日付範囲で絞り込む：DURING
DURINGキーワードを使用すると、日付範囲での日付絞り込みが簡単になります。DURINGキーワードを使用して、暦年や特定の月などの既知の期間、またはブラックフライデー・サイバーマンデーなどの毎年異なる日程の日付範囲でクエリ結果を絞り込むことができます。以下の例をご覧ください。
FROMordersSHOWsum(net_sales)GROUPBYdayALLDURINGbfcm2021DURINGでは、次の特定の日付範囲を指定する演算子を使用できます。
| 日付範囲を指定する演算子 | 機能の用途 |
|---|---|
| 今日 | クエリを実行した日付。 |
| 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年のブラックフライデー・サイバーマンデーの期間の純売上高を比較しています。
FROMordersSHOWsum(net_sales)GROUPBYdayALLDURINGbfcm2022COMPARETObfcm2021データの並べ替え：ORDER BY
ORDER BYキーワード、並びにそのパラメーターである昇順を表すASCや降順を表すDESCを使用すると、クエリから返されたデータを並び替える方法を指定できます。
クエリに含める指標やディメンション (複数のフィールドなど) は、ORDER BYキーワードで指定できます。
たとえば、次のクエリでは、請求先の国や地域ごとの純売上高を、請求先の国、次に各国内の請求先の地域で逆アルファベット順に並べ替えて返します。
FROMordersSHOWnet_salesGROUPBYbilling_country,billing_regionSINCE-1yUNTILtodayORDERBYbilling_country,billing_regionDESC指標やディメンションを記述する順序は重要です。ORDER BYに複数の値を指定すると、その並び替えは記述した順序で指標またはディメンションごとに適用されます。
制限
LIMITキーワードを使用すると、クエリが返す行数を指定できます。これは、各列のデータの概観を単に把握したい場合に役立ちます。あるいは、ORDER BYと組み合わせて上位のリストと下位のリストを作成することができます。
次の例では、LIMITとORDER BYを使用して、過去3か月間で、数量別の売上上位10商品のリストを作成します。
FROMproductsSHOWnet_product_quantityGROUPBYproduct_titleSINCE-3mUNTILtodayORDERBYnet_product_quantityDESCLIMIT10クエリをグラフで表す: VISUALIZEとTYPE
VISUALIZEキーワードを使用すると、折れ線グラフや棒グラフでデータを可視化するShopifyQLクエリを記述できます。
TYPEキーワードの追加は任意ですが、クエリが折れ線グラフまたは棒グラフで可視化されたデータを返すためには、キーワードに「線」または「棒」を含める必要があります。TYPEがクエリに含まれていない場合、ShopifyQLでは、クエリに最適な方法でデータを可視化するよう自動的に決定します。クエリで記述通りに可視化できない場合、ShopifyQLでは表形式のデータを返します。
たとえば、動きを示す折れ線グラフを使用すると、昨年1年間の販売傾向を月別に可視化できます。次に示すクエリでは、昨年の月別の総売上高を示す時系列グラフを返します。総売上高は単線で表示され、x軸は月を、y軸は総売上高を表します。
FROMordersVISUALIZEgross_salesTYPElineGROUPBYmonthALLSINCE-1yUNTILtoday算術演算子
ShopifyQLを使用すると、データにある指標を使用して算術演算を実行できます。次の算術演算子を利用できます。
| 数学演算子 | 機能の用途 |
|---|---|
| + | 2つの数値の加算。 |
| - | 2つの数値の減算。 |
| * | 2つの数値の乗算。 |
| / | 2つの数値の除算。 |
たとえば,次に示すクエリでは,昨年の地域ごとの注文金額を算出します。指標で算術演算子を使用する際は,ASキーワードを使用して新しい指標に新しい名前を割り当てることができます。
FROMordersSHOW(net_sales+returns)ASorder_value,orders,(net_sales+returns)/ordersASsales_per_orderGROUPBYbilling_regionSINCE-1yUNTILtoday関数
ShopifyQL関数を使用すると、Microsoft Excelのピボットテーブルに類似した機能で列を集計できます。列を集計すると、列が組み合わされて新しい値が作成されます。次の関数演算子は、最新バージョンのShopifyQLで利用できます。
| 関数演算子 | 機能の用途 |
|---|---|
| Count() | 結果セットのインスタンス数。 |
| sum() | 結果セットの値の合計。 |
| min() | 結果セットの最小値。 |
| Max() | 結果セットの最大値。 |
| avg() | 結果セットの平均値。 |
sum、min、max、およびavgの関数は数値でのみ使用できます。countはディメンション属性のさまざまなインスタンスをカウントするために使用できます。関数の引数として集計されたフィールドを使用することはできません。集計されたフィールドの末尾は_sum、_count、または_percentです。
たとえば、次のクエリでは販売_合計がすでに集計されているため、エラーを返します。
FROMordersSHOWsum(total_sales)次に示すのは、集計されたフィールドと集計関数を組み合わせた有効なクエリです。
FROMordersSHOWaverage_order_value,sum(gross_sales)GROUPBYbilling_regionSINCE2021-01-01UNTIL2021-12-31このクエリでは、sum関数の結果として、平均注文金額の合計、総売上高の合計を集計して返します。これらの指標は、2021年に行われた注文すべてを請求先の地域別に分類したものです。
コメント
コメントを使用すると、ShopifyQL文の各セクションについて説明したり、ShopifyQL文が実行されないようにしたりすることができます。コメント内のテキストは、実行時間中は無視されます。
単一行のコメントは--で始まり、その行の末尾で終わります。
複数行のコメントは/*で始まり、*/で終わります。
FROMordersSHOWaverage_order_value,sum(gross_sales)-- the line below has been commented out and will not run-- GROUP BY billing_regionWHEREbilling_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 */