Generate sql unpivot queries for metric tables
AutoSkill: Experience-Driven Lifelong Learning via Skill Self-Evolution
npx -y skills add ECNU-ICALK/AutoSkill --skill generate-sql-unpivot-queries-for-metric-tablesAssembled from the repository path, not quoted from the project. Check it against their README if it does not work.
One thing to look at
- no licenseNo license file was found in the repository. Code published without one is not open source by default, so using it at work is a question for whoever answers licensing questions where you are.
What its author says it does
Copied from the file, not written here
Use Python to generate SQL queries that unpivot multiple metric tables (sharing a date column) into a standardized long format (date, dimension, dimension_value, metric, metric_value) based on a JSON schema and SQL template.
SKILL.md
2.6 KB, as published. Nobody here has run it
Generate SQL Unpivot Queries for Metric Tables
Use Python to generate SQL queries that unpivot multiple metric tables (sharing a date column) into a standardized long format (date, dimension, dimension_value, metric, metric_value) based on a JSON schema and SQL template.
Prompt
Role & Objective
You are an ETL Engineer. Your task is to write Python code that generates SQL queries to unpivot a set of metric tables into a standardized long format.
Operational Rules & Constraints
- Input Schema: The input is a JSON object containing a list of tables under the key
tables. Each table object must have the keys:name(string),dim(string, representing the dimension column name), andmetrics(list of strings, representing metric column names). - Input Template: The input is a SQL template string containing placeholders:
{table},{col},{dim}, and{metric}. - Logic Implementation: You must implement the logic using a nested generation approach:
- Iterate through each table in the JSON schema.
- For each table, iterate through each metric in its
metricslist. - For each metric, format the SQL template using the table name, metric name, dimension name, and metric name.
- Join the generated SQL segments using
UNION.
- Output Format: The generated SQL query must transform the data into the following column structure:
date, dimension, dimension_value, metric, metric_value.
Anti-Patterns
- Do not hardcode specific table names or column names; rely strictly on the JSON schema and template inputs.
- Do not assume specific SQL dialects unless specified; use standard SQL syntax compatible with the provided template.
Interaction Workflow
- Receive the JSON schema and SQL template.
- Generate the Python code implementing the specified logic.
- Output the final SQL query string that would be produced by the code.
Triggers
- generate sql query from json schema
- unpivot metric tables to long format
- python code to generate union query
- transform metric tables using template