メニュー
BigQuery-specific SQL patterns: UNNEST for array expansion, STRUCT, ARRAY_AGG, DATE_DIFF/DATE_ADD, backtick-quoted table references, EXCEPT/REPLACE in SELECT, approximate aggregation, partitioned and wildcard tables.
デフォルトでは、最初にソースを確認する Prompt が選択されています。直接コマンドに切り替えるか、ローカルコピーをダウンロードすることもできます。
インストールを決める前に、SKILL.md と SkillsMP に表示されている付属ファイルをお読みください。
Codex または Claude でインストール この Prompt をコピーして Codex、Claude、または他のアシスタントに貼り付けると、Skill ページを確認してインストールできます。
直接コマンドでは確認用 Prompt が省略されます。実行前にソースを確認してください。
npx skills add https://github.com/SignalPilot-Labs/SignalPilot --skill bigquery-sqlコマンドは1行のまま表示されます。コピー前に横へスクロールして全体を確認してください。
ローカルで確認しますか？SkillsMP が現在取得できるファイルをダウンロードできます。
SKILL.md を表示中
Populate the knowledge base from dbt project research. Proposes entries across all 6 categories at org, project, and connection scopes.
Load when working with Xata Postgres branches: forking a branch, building or testing dbt models on a branch, wiring dbt to a branch's credentials, diffing two branches, or the pre-merge impact report. Covers create_xata_branch, delete_xata_branch, get_dbt_profile, xata_branch_diff, schema_diff_branches, and pgroll migrations.
Load at Step 1 before exploring the project. Covers output shape inference, incremental model handling, and what to trust in YML.
SOC 職業分類に基づく
| name | bigquery-sql |
| description | BigQuery-specific SQL patterns: UNNEST for array expansion, STRUCT, ARRAY_AGG, DATE_DIFF/DATE_ADD, backtick-quoted table references, EXCEPT/REPLACE in SELECT, approximate aggregation, partitioned and wildcard tables. |
| type | skill |
-- Full table reference
SELECT * FROM `project.dataset.table`;
-- Can omit project if using the default project
SELECT * FROM `dataset.table`;
-- Explode an array column to rows
SELECT id, item
FROM `project.dataset.table`,
UNNEST(array_col) AS item;
-- UNNEST with offset (position)
SELECT id, item, pos
FROM `project.dataset.table`,
UNNEST(array_col) AS item WITH OFFSET AS pos;
-- UNNEST a literal array
SELECT ([, , ]) num;
-- Add/subtract time
DATE_ADD(order_date, INTERVAL 7 DAY)
DATE_ADD(CURRENT_DATE(), INTERVAL -1 MONTH)
-- Difference between dates
DATE_DIFF(end_date, start_date, DAY)
DATE_DIFF(end_date, start_date, MONTH)
-- Truncate to period
DATE_TRUNC(event_date, MONTH)
TIMESTAMP_TRUNC(event_ts, HOUR)
-- Current date/time
CURRENT_DATE()
CURRENT_TIMESTAMP()
-- All columns except one
SELECT * EXCEPT (col_to_remove) FROM `dataset.table`;
-- Replace a column value inline
SELECT * REPLACE (UPPER(name) AS name) FROM `dataset.table`;
-- Create a STRUCT
SELECT STRUCT(id, name) AS person FROM `dataset.table`;
-- Aggregate rows into an array
SELECT department, ARRAY_AGG(employee_name) AS employees
FROM `dataset.employees`
GROUP BY department;
-- Aggregate into array of structs
SELECT ARRAY_AGG(STRUCT(id, name)) AS records FROM `dataset.table`;
-- Approximate distinct count (faster for large tables)
APPROX_COUNT_DISTINCT(user_id)
-- Approximate quantiles
APPROX_QUANTILES(value, 100)[OFFSET(50)] -- median
When querying partitioned tables, always filter on the partition column to avoid full-table scans:
-- Partition on _PARTITIONDATE (pseudo-column)
WHERE _PARTITIONDATE >= '2024-01-01'
-- Partition on a date column
WHERE event_date BETWEEN '2024-01-01' AND '2024-12-31'
-- Query all date-sharded tables matching a prefix
SELECT * FROM `project.dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20241231';
REGEXP_EXTRACT(col, r'pattern') -- extract first match
REGEXP_REPLACE(col, r'pattern', 'repl') -- replace matches
SPLIT(col, ',')[SAFE_OFFSET(0)] -- split, access by index
TRIM(col) / LTRIM(col) / RTRIM(col)
FORMAT('%s-%d', str_col, int_col) -- printf-style formatting
= NULL — use IS NULLCOUNT(DISTINCT ...) on huge tables — use APPROX_COUNT_DISTINCTSTRING_AGG(col, ',' ORDER BY col) for string aggregation (not GROUP_CONCAT).IF(condition, true_val, false_val) — often cleaner than CASE WHEN for simple conditions.SELECT * FROM dataset.INFORMATION_SCHEMA.COLUMNS for metadata queries — useful when schema_overview is insufficient.spider2-public-data. Table references: spider2-public-data.{dataset}.{table}tags column (e.g., |python|python-2.7|).
To filter for Python 2 specific questions (excluding Python 3):
WHERE REGEXP_CONTAINS(tags, r'python-2') AND NOT REGEXP_CONTAINS(tags, r'python-3')