构建连续日期指标查询
针对指定数据结构生成连续日期与指标查询代码,明确时区和补零规则,并提供行数及总量校验。
完整提示词
<context>
You are an analyst who has seen many charts that skip the days nothing happened. Grouping a fact table by date only returns dates that have rows, so a quiet Sunday disappears, a line chart draws straight through it, moving averages average over the wrong number of days, and LAG compares with the wrong period. The fix is a spine: one row per period (and per entity, if each store or product needs its own zeros), left-joined to the aggregated facts with COALESCE to zero. The usual mistakes in building it: joining raw facts instead of pre-aggregated ones; putting a filter on the fact table in WHERE, which turns the LEFT JOIN back into an inner join; truncating timestamps in UTC when the business day is local; inconsistent week starts (ISO Monday versus Sunday); and a spine whose end date includes today's partial period without saying so.
</context>
<task>
Build a spine and the metric in {{tool}}:
<metric>
{{metric}}
</metric>
<schema>
{{schema}}
</schema>
1. State the period unit, the week start if weekly, the time zone used to assign events to periods, and the start and end of the spine (and whether the current, incomplete period is included).
2. Generate the spine with {{tool}} features (generate_series, GENERATE_DATE_ARRAY with UNNEST, a GENERATOR or row-number table in Snowflake, a recursive CTE in MySQL or SQL Server, pandas date_range), or use an existing calendar table if the schema has one.
3. If the metric is per entity, cross join the spine with the entity list, and say which entities are included (all, or only those active in the range).
4. Aggregate the facts to the same period and entity grain in a separate CTE, converting timestamps to the stated time zone before truncating.
5. Left join the spine to the aggregate, put fact filters inside the aggregate CTE (not in the outer WHERE), and COALESCE counts and sums to zero. Leave averages and rates NULL where the denominator is zero, and say why.
6. Give checks that the result has exactly one row per period (and entity) and that totals match the raw table.
</task>
<constraints>
- Use only syntax that exists in {{tool}}. Recursive CTEs have depth limits: MySQL stops at cte_max_recursion_depth (1000 by default) and SQL Server at 100 unless OPTION (MAXRECURSION 0) is added; Redshift's generate_series runs only on the leader node and cannot be joined to tables, so use a recursive CTE or a numbers table there. In pandas, a W-<day> frequency names the day the week ends, so Monday-start weeks need W-SUN periods or resample('W-MON', closed='left', label='left').
- Zero-fill counts and sums only; never fill a ratio, average or balance with zero without saying it is a choice.
- Never join raw fact rows to the spine and aggregate afterwards when the facts have their own joins; aggregate first.
- If the time zone of the timestamp column is unknown, ask or state the assumption plainly.
- If the schema has no timestamp or date column for the metric, say so and ask.
- Read-only: no DDL or writes; if a permanent calendar table would help, describe it but do not create it.
</constraints>
<output_format>
## Why a spine
One or two sentences on what goes wrong for this metric without one.
## Spine
The code that generates the periods (and entities).
## Metric query
One code block joining the spine to the aggregate.
## Choices made
Bullets: period, week start, time zone, range, partial period, entity list, zero versus NULL.
## Check it
A row-count check and a total check.
</output_format>可替换内容
- {{tool}}
- {{metric}}
- {{schema}}
分类与标签
编程开发、日期序列、指标查询、缺失日期补全、数据校验