Zenn
Open14

論理プログラミング言語Logicaでデータサイエンス100本ノック

koji_matskoji_mats

Googleが発表したOSSプロジェクトである論理プログラミング言語Logicaを使って、データサイエンス100本ノック(構造化データ加工編)の設問を解きながらどのような言語かを確認していく。
(BigQueryのクエリとして実行していく)

最初に、プログラミング言語Logicaの特徴を纏めておく。

  • 論理型プログラミング言語: このカテゴリではPrologが有名
  • SQLにコンパイルされる: 現状BigQueryとPostgreSQLに対応
  • モジュール機構がある: SQLと比較した強み
  • コンパイラはPythonで書かれている: Jupyter NotebookやGoogle Colabですぐ始められる

Colabでチュートリアルが用意されているので、まずこちらからやると良いと思う。

コードの見た目は関係論理の記述に似ている。

事前に、データサイエンス100本ノックのテーブルデータをBigQueryのデータセットmy_project.100knockにロードしている前提で書いていく。

実行環境はColabが簡単に始められて良い。
LogicaでBigQueryに対してクエリを実行するためにColab上で以下の事前準備を行っておく必要がある。

# Install Logica.
!pip install logica

# Connect to the database.
from google.colab import auth
from logica import colab_logica

# GCP authentication
auth.authenticate_user()
project_id = 'my_project'
colab_logica.SetProject(project_id)

あとは、Logicaのコードを入力するセルにマジックコマンドとして以下のように記述しておけば実行可能となる。<出力テーブル名>を指定しておくとその結果がテーブル形式で表示され、更にコンパイルされたSQLも確認できる。

%%logica <出力テーブル名>
koji_matskoji_mats

全カラム出力とLIMIT句

S-001: レシート明細テーブル（receipt）から全項目を10件抽出し、どのようなデータを保有しているか目視で確認せよ。

@Limit(S001, 10);
S001(..r) :- my_project.100knock.receipt(..r);

:-の左側に目的の変数、右側にその変数の条件式、というような書き方をする。
..rは全列を表しており、my_project.100knock.receipt(..r)と書いた場合、..rはmy_project.100knock.receiptテーブルの全列を意味する。@Limit()で結果抽出数を指定している。
これは以下のようなSQLにコンパイルされる。

SELECT
  my_project_100knock_receipt.*
FROM
  my_project.100knock.receipt AS my_project_100knock_receipt
LIMIT
  10;
koji_matskoji_mats

特定のカラムを出力

S-002: レシート明細のテーブル（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、10件表示させよ。

@Limit(S002, 10);
S002(sales_ymd:, customer_id:, product_cd:, amount:) :-
  my_project.100knock.receipt(sales_ymd:, customer_id:, product_cd:, amount:);

sales_ymd:, customer_id:, product_cd:, amount:は、sales_ymd: sales_ymd, customer_id: customer_id, product_cd: product_cd, amount: amountの省略形。
意味的には<列名>: <変数名>という感じ。

koji_matskoji_mats

列名のエイリアス

S-003: レシート明細のテーブル（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、10件表示させよ。ただし、sales_ymdはsales_dateに項目名を変更しながら抽出すること。

@Limit(S003, 10);
S003(sales_date: sales_ymd, customer_id:, product_cd:, amount:) :- 
  my_project.100knock.receipt(sales_ymd:, customer_id:, product_cd:, amount:);

sales_date: sales_ymdとすれば、変数=sales_ymdを列名=sales_dateとして出力する。

koji_matskoji_mats

レコード抽出条件

S-004: レシート明細のテーブル（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、以下の条件を満たすデータを抽出せよ
顧客ID（customer_id）が"CS018205000001"

@Limit(S004, 10);
S004(sales_ymd:, customer_id:, product_cd:, amount:) :-
  customer_id == "CS018205000001",
  my_project.100knock.receipt(sales_ymd:, customer_id:, product_cd:, amount:);

:-の右側に<式1>, <式2>, ...のように式を並べて書くとAND条件となる。(<式1>∧<式2>∧...の意味)
以下のようなSQLにコンパイルされる。

SELECT
  my_project_100knock_receipt.sales_ymd AS sales_ymd,
  "CS018205000001" AS customer_id,
  my_project_100knock_receipt.product_cd AS product_cd,
  my_project_100knock_receipt.amount AS amount
FROM
  my_project.100knock.receipt AS my_project_100knock_receipt
WHERE
  my_project_100knock_receipt.customer_id = "CS018205000001"
LIMIT
  10;

S-005: レシート明細のテーブル（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、以下の条件を満たすデータを抽出せよ。
顧客ID（customer_id）が"CS018205000001" 売上金額（amount）が1,000以上

@Limit(S005, 10);
S005(sales_ymd:, customer_id:, product_cd:, amount:) :-
  customer_id == "CS018205000001",
  amount >= 1000,
  my_project.100knock.receipt(sales_ymd:, customer_id:, product_cd:, amount:);

S-006: レシート明細テーブル（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上数量（quantity）、売上金額（amount）の順に列を指定し、以下の条件を満たすデータを抽出せよ。
・顧客ID（customer_id）が"CS018205000001"
・売上金額（amount）が1,000以上または売上数量（quantity）が5以上

@Limit(S006, 10);
S006(sales_ymd:, customer_id:, product_cd:, quantity:, amount:) :-
  customer_id == "CS018205000001",
  amount >= 1000 | quantity >= 5,
  my_project.100knock.receipt(sales_ymd:, customer_id:, product_cd:, quantity:, amount:);

OR条件は|を使う。

S-007: レシート明細のテーブル（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、以下の条件を満たすデータを抽出せよ。
・顧客ID（customer_id）が"CS018205000001"
・売上金額（amount）が1,000以上2,000以下

@Limit(S007, 10);
S007(sales_ymd:, customer_id:, product_cd:, amount:) :-
  customer_id == "CS018205000001",
  amount >= 1000, amount <= 2000,
  my_project.100knock.receipt(sales_ymd:, customer_id:, product_cd:, amount:);

S-008: レシート明細テーブル（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、以下の条件を満たすデータを抽出せよ
・顧客ID（customer_id）が"CS018205000001"
・商品コード（product_cd）が"P071401019"以外

@Limit(S008, 10);
S008(sales_ymd:, customer_id:, product_cd:, amount:) :-
  customer_id == "CS018205000001",
  product_cd != "P071401019",
  my_project.100knock.receipt(sales_ymd:, customer_id:, product_cd:, amount:);

S-009: 以下の処理において、出力結果を変えずにORをANDに書き換えよ。
select * from store where not (prefecture_cd = '13' or floor_area > 900)

@Limit(S009, 10);
S009(store_cd:, store_name:, prefecture_cd:, prefecture:, address:, address_kana:, tel_no:, longitude:, latitude:, floor_area:) :-
  prefecture_cd != "13",
  floor_area <= 900,
  my_project.100knock.store(store_cd:, store_name:, prefecture_cd:, prefecture:, address:, address_kana:, tel_no:, longitude:, latitude:, floor_area:);
koji_matskoji_mats

LIKE

S-010: 店舗テーブル（store）から、店舗コード（store_cd）が"S14"で始まるものだけ全項目抽出し、10件だけ表示せよ。

@Limit(S010, 10);
S010(store_cd:, ..r) :-
  Like(store_cd, "S14%"),
  my_project.100knock.store(store_cd:, ..r);

ビルイトイン関数LikeはSQLのLIKEにコンパイルされる。

SELECT
  my_project_100knock_store.store_cd AS store_cd,
  (SELECT AS STRUCT my_project_100knock_store.* EXCEPT (store_cd)).*
FROM
  my_project.100knock.store AS my_project_100knock_store
WHERE
  (my_project_100knock_store.store_cd LIKE "S14%")
LIMIT
  10;

条件式でstore_cdを使うのでstore_cd:, ..rという指定の仕方をした。

S-011: 顧客テーブル（customer）から顧客ID（customer_id）の末尾が1のものだけ全項目抽出し、10件だけ表示せよ。

@Limit(S011, 10);
S011(customer_id:, ..r) :-
  Like(customer_id, "%1"),
  my_project.100knock.customer(customer_id:, ..r);

S-012: 店舗テーブル（store）から横浜市の店舗だけ全項目表示せよ。

@Limit(S012, 10);
S012(address:, ..r) :-
  Like(address, "%横浜市%"),
  my_project.100knock.store(address:, ..r);
koji_matskoji_mats

正規表現マッチ

S-013: 顧客テーブル（customer）から、ステータスコード（status_cd）の先頭がアルファベットのA〜Fで始まるデータを全項目抽出し、10件だけ表示せよ。

@Limit(S013, 10);
S013(status_cd:, age:, ..r) :-
  RegexpContains(status_cd, "^[A-F]") == true,
  my_project.100knock.customer(status_cd:, age:, ..r);

ビルトイン関数RegexpContainsを使う。条件部分にRegexpContains(...)だけ指定するとRegexpContainsというテーブル名として認識されてしまうようなので== trueとしている。

S-014: 顧客テーブル（customer）から、ステータスコード（status_cd）の末尾が数字の1〜9で終わるデータを全項目抽出し、10件だけ表示せよ。

@Limit(S014, 10);
S014(status_cd:, age:, ..r) :-
  RegexpContains(status_cd, "[1-9]$") == true,
  my_project.100knock.customer(status_cd:, age:, ..r);

S-015: 顧客テーブル（customer）から、ステータスコード（status_cd）の先頭がアルファベットのA〜Fで始まり、末尾が数字の1〜9で終わるデータを全項目抽出し、10件だけ表示せよ。

@Limit(S015, 10);
S015(status_cd:, ..r) :-
  RegexpContains(status_cd, "^[A-F].*[1-9]$") == true,
  my_project.100knock.customer(status_cd:, ..r);

S-016: 店舗テーブル（store）から、電話番号（tel_no）が3桁-3桁-4桁のデータを全項目表示せよ。

S016(tel_no:, ..r) :-
  RegexpContains(tel_no, "[0-9]{3}-[0-9]{3}-[0-9]{4}") == true,
  my_project.100knock.store(tel_no:, ..r);
koji_matskoji_mats

ソート

S-017: 顧客テーブル（customer）を生年月日（birth_day）で高齢順にソートし、先頭10件を全項目表示せよ。

@OrderBy(S017, "birth_day");
@Limit(S017, 10);
S017(..r) :-
  my_project.100knock.customer(..r);

@OrderBy()で表示順序を指定する。以下のようにORDER BY句にコンパイルされる。

SELECT
  my_project_100knock_customer.*
FROM
  my_project.100knock.customer AS my_project_100knock_customer
ORDER BY
  birth_day
LIMIT
  10;

S-018: 顧客テーブル（customer）を生年月日（birth_day）で若い順にソートし、先頭10件を全項目表示せよ。

@OrderBy(S018, "birth_day desc");
@Limit(S018, 10);
S018(..r) :-
  my_project.100knock.customer(..r);

降順に表示する場合は、カラム名にdescを追加するだけ。

koji_matskoji_mats

WINDOW関数

S-019: レシート明細テーブル（receipt）に対し、1件あたりの売上金額（amount）が高い順にランクを付与し、先頭10件を抽出せよ。項目は顧客ID（customer_id）、売上金額（amount）、付与したランクを表示させること。なお、売上金額（amount）が等しい場合は同一順位を付与するものとする。

@OrderBy(S019, "ranking");
@Limit(S019, 10);
S019(customer_id:, amount:, ranking:) :-
  ranking ==  SqlExpr("RANK() OVER (ORDER BY amount DESC)", {}),
  my_project.100knock.receipt(customer_id:, amount:);

SQLではWINDOW関数RANK()を使って表現したいところだが、現状Logicaでは実装されていないよう。このような場合は、該当のカラムを表現するSQLをビルイトイン関数SqlExpr()で作って指定する。今回は、rankingカラムはSQL「RANK() OVER (ORDER BY amount DESC)」であるという条件を書いている。

以下のようにコンパイルされる。

SELECT
  my_project_100knock_receipt.customer_id AS customer_id,
  my_project_100knock_receipt.amount AS amount,
  RANK() OVER (ORDER BY amount DESC) AS ranking
FROM
  my_project.100knock.receipt AS my_project_100knock_receipt
ORDER BY
  ranking
LIMIT
  10;

S-020: レシート明細テーブル（receipt）に対し、1件あたりの売上金額（amount）が高い順にランクを付与し、先頭10件を抽出せよ。項目は顧客ID（customer_id）、売上金額（amount）、付与したランクを表示させること。なお、売上金額（amount）が等しい場合でも別順位を付与すること。

@Limit(S020, 10);
S020(customer_id:, amount:, ranking:) :-
  ranking == SqlExpr("ROW_NUMBER() OVER (ORDER BY amount DESC)", {}),
  my_project.100knock.receipt(customer_id:, amount:);

WINDOW関数についてはまだシンタックスが決まっていないようなので今後に期待。

koji_matskoji_mats

集計

Logica = Logic + Aggregation.

Logicaは論理プログラミングのシンタックスに集計(Aggregation)の機能を導入して拡張しているとのこと。

S-021: レシート明細テーブル（receipt）に対し、件数をカウントせよ。

S021() += 1 :- my_project.100knock.receipt();

1つの集計値を算出したい場合は、今までカラム名を指定していた部分(S021())の外側で:-の手前に、<集計関数名>= <集計関数への入力値>の形式で集計値を記述する。今回の場合、+は合計を取る関数(SUM)で入力値は定数の1となる。
これは、以下のSQLにコンパイルされる。

SELECT
  SUM(1) AS logica_value
FROM
  my_project.100knock.receipt AS my_project_100knock_receipt;

集計値が１つの場合は、集計結果にlogica_valueという列名が与えられる。
なお、SQLだと一般的にはCOUNT(*)で書くことが多いが、上記クエリでも同じ意味でBigQueryのデータ走査量は0Byteになると思う。

S-022: レシート明細テーブル（receipt）の顧客ID（customer_id）に対し、ユニーク件数をカウントせよ。

S022() ExactCount= customer_id :- my_project.100knock.receipt(customer_id:);

ExactCountはCOUNT(DISTINCT ...)にコンパイルされる。

SELECT
  COUNT(DISTINCT my_project_100knock_receipt.customer_id) AS logica_value
FROM
  `my_project.100knock.receipt` AS my_project_100knock_receipt;

S-023: レシート明細テーブル（receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）と売上数量（quantity）を合計せよ。

S023(store_cd:, amount? += amount, quantity? += quantity) distinct :-
  my_project.100knock.receipt(store_cd:, amount:, quantity:);

集計対象が複数カラムある場合は、カラム名を記述する部分に<カラム名>? <集計関数名>= <集計関数への入力値>の形式で記述し、:-の前にdistinctを指定する。以下コンパイル結果。

SELECT
  my_project_100knock_receipt.store_cd AS store_cd,
  SUM(my_project_100knock_receipt.amount) AS amount,
  SUM(my_project_100knock_receipt.quantity) AS quantity
FROM
  `my_project.100knock.receipt` AS my_project_100knock_receipt
GROUP BY
  store_cd;

S-024: レシート明細テーブル（receipt）に対し、顧客ID（customer_id）ごとに最も新しい売上日（sales_ymd）を求め、10件表示せよ。

@Limit(S024, 10);
S024(customer_id:, sales_ymd? Max= sales_ymd) distinct :-
  my_project.100knock.receipt(customer_id:, sales_ymd:);

S-025: レシート明細テーブル（receipt）に対し、顧客ID（customer_id）ごとに最も古い売上日（sales_ymd）を求め、10件表示せよ。

@Limit(S025, 10);
S025(customer_id:, sales_ymd? Min= sales_ymd) distinct :-
  my_project.100knock.receipt(customer_id:, sales_ymd:);

S-026: レシート明細テーブル（receipt）に対し、顧客ID（customer_id）ごとに最も新しい売上日（sales_ymd）と古い売上日を求め、両者が異なるデータを10件表示せよ。

LatestAndEarliest(customer_id:, max? Max= sales_ymd, min? Min= sales_ymd) distinct :-
  my_project.100knock.receipt(customer_id:, sales_ymd:);

@Limit(S026, 10);
S026(customer_id:, max:, min:) :-
  max != min,
  LatestAndEarliest(customer_id:, max:, min:);

S-027: レシート明細テーブル（receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）の平均を計算し、降順でTOP5を表示せよ。

@OrderBy(S027, "avg_amount desc");
@Limit(S027, 5);
S027(store_cd:, avg_amount? Avg= amount) distinct :-
  my_project.100knock.receipt(store_cd:, amount:);

S-033: レシート明細テーブル（receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）の平均を計算し、330以上のものを抽出せよ。

Averages(store_cd:, amount_avg? Avg= amount) distinct :-
  my_project.100knock.receipt(store_cd:, amount:);

S033(store_cd:, amount_avg:) :-
  amount_avg >= 330,
  Averages(store_cd:, amount_avg:);

S-034: レシート明細テーブル（receipt）に対し、顧客ID（customer_id）ごとに売上金額（amount）を合計して全顧客の平均を求めよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。

CustomerAmount(customer_id:, sum_amount? += amount) distinct :-
  ~Like(customer_id, "Z%"),
  my_project.100knock.receipt(customer_id:, amount:);

S034() Avg= sum_amount :- CustomerAmount(sum_amount:);

S-035: レシート明細テーブル（receipt）に対し、顧客ID（customer_id）ごとに販売金額（amount）を合計して全顧客の平均を求め、平均以上に買い物をしている顧客を抽出せよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。なお、データは10件だけ表示させれば良い。

CustomerAmount(customer_id:, sum_amount? += amount) distinct :-
  ~Like(customer_id, "Z%"),
  my_project.100knock.receipt(customer_id:, amount:);

AmountAverage() Avg= sum_amount :- CustomerAmount(sum_amount:);

@Limit(S035, 10);
S035(customer_id:, sum_amount:) :-
  sum_amount >= AmountAverage(),
  CustomerAmount(customer_id:, sum_amount:);
koji_matskoji_mats

集計時の明示的WITH

S-028: レシート明細テーブル（receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）の中央値を計算し、降順でTOP5を表示せよ。

以下はコンパイルされるが、コンパイルされたSQLがBigQueryでシンタックスエラーとなる。

Medians(store_cd:, median:) :-
  median == SqlExpr("PERCENTILE_CONT({amount}, 0.5) OVER (PARTITION BY {store_cd})", {amount:, store_cd:}),
  my_project.100knock.receipt(store_cd:, amount:);

@OrderBy(S028, "median DESC");
@Limit(S028, 5);
S028(store_cd:, median? AnyValue= median) distinct :-
  Medians(store_cd:, median:);

コンパイルされたSQLを見ると、集計関数(ANY_VALUE)の引数にWINDOW関数(PERCENTILE_CONT)が指定されているのでこれはシンタックスエラーである。

SELECT
  my_project_100knock_receipt.store_cd AS store_cd,
  ANY_VALUE(PERCENTILE_CONT(my_project_100knock_receipt.amount, 0.5) OVER (PARTITION BY my_project_100knock_receipt.store_cd)) AS median
FROM
  my_project.100knock.receipt AS my_project_100knock_receipt
GROUP BY
  store_cd
ORDER BY
  median DESC
LIMIT
  5;

MediansをWITH句で別分けにしたいので@With()で明示する。

@With(Medians);
Medians(store_cd:, median:) :-
  median == SqlExpr("PERCENTILE_CONT({amount}, 0.5) OVER (PARTITION BY {store_cd})", {amount:, store_cd:}),
  my_project.100knock.receipt(store_cd:, amount:);

@OrderBy(S028, "median DESC");
@Limit(S028, 5);
S028(store_cd:, median? AnyValue= median) distinct :-
  Medians(store_cd:, median:);

意図通りにコンパイルされた。

WITH t_0_Medians AS (
SELECT
  my_project_100knock_receipt.store_cd AS store_cd,
  PERCENTILE_CONT(my_project_100knock_receipt.amount, 0.5)
    OVER (PARTITION BY my_project_100knock_receipt.store_cd) AS median
FROM
  my_project.100knock.receipt AS my_project_100knock_receipt
)
SELECT
  Medians.store_cd AS store_cd,
  ANY_VALUE(Medians.median) AS median
FROM
  t_0_Medians AS Medians
GROUP BY
  store_cd
ORDER BY
  median DESC
LIMIT
  5;

S-029: レシート明細テーブル（receipt）に対し、店舗コード（store_cd）ごとに商品コードの最頻値を求めよ。

@With(ProductCount);
ProductCount(store_cd:, product_cd:, mode_count? += 1) distinct :-
  my_project.100knock.receipt(store_cd:, product_cd:);

@With(ProductMode);
ProductMode(store_cd:, product_cd:, mode_count:, rnk:) :-
  rnk == SqlExpr("RANK() OVER (PARTITION BY {store_cd} ORDER BY {mode_count} DESC)", {store_cd:, mode_count:}),
  ProductCount(store_cd:, product_cd:, mode_count:);

@OrderBy(S029, "store_cd, product_cd");
@Limit(S029, 10);
S029(store_cd:, product_cd:, mode_count:) :-
  rnk == 1,
  ProductMode(store_cd:, product_cd:, rnk:, mode_count:);
koji_matskoji_mats

統計集計関数

BigQueryで使える統計集計関数も一通り使えるらしい。

S-030: レシート明細テーブル（receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）の標本分散を計算し、降順にTOP5を表示せよ。

@OrderBy(S030, "vars_amount desc");
@Limit(S030, 5);
S030(store_cd:, vars_amount? VarSamp= amount) distinct :-
  my_project.100knock.receipt(store_cd:, amount:);

S-031: レシート明細テーブル（receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）の標本標準偏差を計算し、降順にTOP5を表示せよ。

@OrderBy(S031, "stds_amount DESC");
@Limit(S031, 5);
S031(store_cd:, stds_amount? StddevSamp= amount) distinct :-
  my_project.100knock.receipt(store_cd:, amount:);

S-032: レシート明細テーブル（receipt）に対し、売上金額（amount）について25％刻みでパーセンタイル値を求めよ。

@With(Percentiles);
Percentiles(amount_25per:, amount_50per:, amount_75per:, amount_100per:) :-
  amount_25per == SqlExpr("PERCENTILE_CONT({amount}, 0.25) OVER ()", {amount:}),
  amount_50per == SqlExpr("PERCENTILE_CONT({amount}, 0.50) OVER ()", {amount:}),
  amount_75per == SqlExpr("PERCENTILE_CONT({amount}, 0.75) OVER ()", {amount:}),
  amount_100per == SqlExpr("PERCENTILE_CONT({amount}, 1.00) OVER ()", {amount:}),
  my_project.100knock.receipt(amount:);

S032(amount_25per? AnyValue= amount_25per,
     amount_50per? AnyValue= amount_50per,
     amount_75per? AnyValue= amount_75per,
     amount_100per? AnyValue= amount_100per) distinct :-
  Percentiles(amount_25per:, amount_50per:, amount_75per:, amount_100per:);
koji_matskoji_mats

内部結合

S-036: レシート明細テーブル（receipt）と店舗テーブル（store）を内部結合し、レシート明細テーブルの全項目と店舗テーブルの店舗名（store_name）を10件表示させよ。

@Limit(S036, 10);
S036(sales_ymd:, sales_epoch:, store_cd:, receipt_no:, receipt_sub_no:, customer_id:, product_cd:, quantity:, amount:, store_name:) :-
  my_project.100knock.receipt(sales_ymd:, sales_epoch:, store_cd:, receipt_no:, receipt_sub_no:, customer_id:, product_cd:, quantity:, amount:),
  my_project.100knock.store(store_cd:, store_name:);

store_cdでreceiptテーブルとstoreテーブルを内部結合している。以下のようにコンパイルされる。

SELECT
  my_project_100knock_receipt.sales_ymd AS sales_ymd,
  my_project_100knock_receipt.sales_epoch AS sales_epoch,
  my_project_100knock_receipt.store_cd AS store_cd,
  my_project_100knock_receipt.receipt_no AS receipt_no,
  my_project_100knock_receipt.receipt_sub_no AS receipt_sub_no,
  my_project_100knock_receipt.customer_id AS customer_id,
  my_project_100knock_receipt.product_cd AS product_cd,
  my_project_100knock_receipt.quantity AS quantity,
  my_project_100knock_receipt.amount AS amount,
  my_project_100knock_store.store_name AS store_name
FROM
  my_project.100knock.receipt AS my_project_100knock_receipt,
  my_project.100knock.store AS my_project_100knock_store
WHERE
  my_project_100knock_store.store_cd = my_project_100knock_receipt.store_cd
LIMIT
  10;

S-037: 商品テーブル（product）とカテゴリテーブル（category）を内部結合し、商品テーブルの全項目とカテゴリテーブルの小区分名（category_small_name）を10件表示させよ。

@Limit(S037, 10);
S037(product_cd:, category_major_cd:, category_medium_cd:, category_small_cd:, unit_price:, unit_cost:, category_small_name:) :-
  my_project.100knock.product(product_cd:, category_major_cd:, category_medium_cd:, category_small_cd:, unit_price:, unit_cost:),
  my_project.100knock.category(category_small_cd:, category_small_name:);

category_small_cdでproductテーブルとcategoryテーブルを内部結合している。

koji_matskoji_mats

左外部結合

S-038: 顧客テーブル（customer）とレシート明細テーブル（receipt）から、各顧客ごとの売上金額合計を求めよ。ただし、買い物の実績がない顧客については売上金額を0として表示させること。また、顧客は性別コード（gender_cd）が女性（1）であるものを対象とし、非会員（顧客IDが'Z'から始まるもの）は除外すること。なお、結果は10件だけ表示させれば良い。

CustomerAmount(customer_id:, sum_amount? += amount) distinct :-
  my_project.100knock.receipt(customer_id:, amount:);

@Limit(S038, 10);
S038(customer_id:, sum_amount: Coalesce(sum_amount, 0)) :-
  gender_cd == "1",
  ~Like(customer_id, "Z%"),
  my_project.100knock.customer(customer_id:, gender_cd:),
  sum_amount AnyValue= (v :- CustomerAmount(customer_id:, sum_amount: v));

ポイントは、sum_amount AnyValue= (v :- CustomerAmount(customer_id:, sum_amount: v))の部分。
<変数> <集計関数>= (<集計関数への入力値> :- <条件ボディ>)の形式で、intra-record aggregationと呼ぶらしい。SQLだと以下のように、内部で集計するサブクエリにコンパイルされる。

WITH t_0_CustomerAmount AS (
SELECT
  my_project_100knock_receipt.customer_id AS customer_id,
  SUM(my_project_100knock_receipt.amount) AS sum_amount
FROM
  my_project.100knock.receipt AS my_project_100knock_receipt
GROUP BY customer_id
)
SELECT
  my_project_100knock_customer.customer_id AS customer_id,
  COALESCE(
    (SELECT
        ANY_VALUE(CustomerAmount.sum_amount) AS logica_value
      FROM
        t_0_CustomerAmount AS CustomerAmount
      WHERE
        CustomerAmount.customer_id = my_project_100knock_customer.customer_id),
    0) AS sum_amount
FROM
  my_project.100knock.customer AS my_project_100knock_customer
WHERE
  ((SELECT
    MIN(1) AS logica_value
  FROM
    UNNEST(ARRAY['UNUSED']) as unused_unnest
  WHERE
    (my_project_100knock_customer.customer_id LIKE "Z%")) IS NULL) AND
  my_project_100knock_customer.gender_cd = "1" LIMIT 10;

別解として以下も考えられる。

CustomerAmount(customer_id:, sum_amount? += amount) distinct :-
  my_project.100knock.receipt(customer_id:, amount:);

TargetCustomer(customer_id:) :-
  gender_cd == "1",
  ~Like(customer_id, "Z%"),
  my_project.100knock.customer(customer_id:, gender_cd:);

OnlyLeft(customer_id:, sum_amount: null) :-
  TargetCustomer(customer_id:),
  ~CustomerAmount(customer_id:);

@Limit(AnotherSolution, 10);
AnotherSolution(customer_id:, sum_amount: Coalesce(sum_amount, 0)) :-
  (TargetCustomer(customer_id:), CustomerAmount(customer_id:, sum_amount:)) |
  OnlyLeft(customer_id:, sum_amount:);

前者の方が直感的で読みやすいと思う。