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

# 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 <出力テーブル名>

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

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

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

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

@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:);

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;

@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:);

@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:);

@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:);

@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:);

@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:);

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

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;

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

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

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

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

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

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

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

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

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

@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:);

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;

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

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

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

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

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

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

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;

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

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

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:);

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

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:);

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:);

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:);

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:);

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;

@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;

@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:);

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

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

@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:);

@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:);

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;

@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:);

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));

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:);