Metabase 按「年周」分组与 week 表达式完全指南：周数算法、三种模式与 SQL 实现原理

Metabase 按「年周」分组与 week 表达式完全指南周数算法、三种模式与 SQL 实现原理【免费下载链接】metabaseThe easy-to-use open source Business Intelligence and Embedded Analytics tool that lets everyone work with data :bar_chart:项目地址: https://gitcode.com/GitHub_Trending/me/metabase本指南围绕 Metabase 查询构建器中的「Week of year年周」分组功能与week自定义表达式展开系统讲解周数1~52/53/54的编号规则、查询构建器与表达式两种使用路径、ISO/US/Instance三种周数计算算法及其跨年差异并结合当前仓库源码说明其底层实现。读完你将能够在 Metabase 中准确完成按年周汇总例如做同比周对比、为实例定制周的起始日并理解不同数据库在周数计算上的行为差异。什么是「年周」Week of year一年中的周通常按 1 到 52、53 或 54 编号——具体范围取决于「一年中的第一周」如何界定。Metabase 支持多种算法来确定一年中的第一周因此同一日期在不同算法下可能落在不同的周数里。在 Metabase 中你可以通过两种方式使用年周查询构建器分组将汇总结果按「Week of year」分桶输出的是周序号如第 1 周、第 2 周而不是像「by week按周」那样输出具体的周日期区间。自定义表达式使用week函数从日期列中提取周序号整数并可显式指定计算算法。按年周分组在做**周期对比period-over-period**时非常有用——例如把今年第 1 周的某个指标与去年第 1 周的同一指标进行比较周序号能保证两个年份的「第 N 周」对齐。在查询构建器中按「Week of year」汇总在查询构建器Notebook Editor中按年周分组的操作步骤如下在Summarize汇总部分点击Pick a column to group by选择一个日期字段点击字段名右侧的日期粒度下拉框例如默认显示的 by month在弹出的日期粒度选项中点击More...展开更多选项然后选择Week of year。完成后结果将按周序号汇总例如 Week 1、Week 2而不是像选择 by week 时那样按具体的周日期区间展示从源码结构可以印证这一点在 src/metabase/lib/schema/temporal_bucketing.cljc 中ordered-date-extraction-units前端按此顺序展示的可提取日期单位明确包含:week-of-year并且这些提取单位返回的是整数Extraction units return integers!与:week截断单位、返回日期是两类不同的单位——这正是「按年周」返回周序号而非周日期的根本原因。国际化文案:week-of-year Week of year / Weeks of year也定义在 src/metabase/lib/temporal_bucket.cljc 中。Metabase 默认如何为一年中的周编号默认情况下查询构建器中的「Week of year」分组采用如下规则Metabase 会找到一年中的第一个周日将其所在的那一周称为「第 1 周」第一个周日之前的任何一天都被视为**上一年最后一周第 52 或 53 周**的一部分。也就是说默认算法下跨年的前几天的周序号可能仍然属于前一年。如何改用不同的「一年第一周」算法即便你在 本地化设置 中为实例配置了不同的「一周的第一天」默认是周日查询构建器里的「Week of year」分组仍然始终以周日作为一周的起始日不会跟随本地化设置变化。想要让周起始日跟随实例的本地化设置需要在自定义表达式中使用week函数并指定Instance模式例如week([Created At], Instance)此外week自定义表达式还额外提供了两种可选的「一年第一周」算法详见下文。如果希望在汇总分组时使用替代算法可以先用week表达式创建一个自定义列Custom column提取周序号再按该自定义列分组。week自定义表达式week自定义表达式从日期列中提取一年中的第几周返回值为整数。语法week(column, mode)示例week([Created At]) week([Created At], US) week([Created At], Instance)参数说明参数说明column要提取周序号的日期列mode可选参数指定「一年第一周」的计算算法取值如下mode的三种取值ISO默认值一年中的第一周是包含当年第一个周四的那一周一周从周一开始。这符合 ISO 8601 标准。US一年中的第一周从 1 月 1 日开始一周从周日开始。大多数年份里第一周会是不完整的部分周。Instance一年中的第一周从 1 月 1 日开始一周从本地化设置中指定的一周起始日开始。大多数年份里第一周会是不完整的部分周。需要注意当前可用的这三种模式没有一种与查询构建器中「Group by Week of year」默认使用的周数算法完全一致。因此在同一实例上表达式结果与分组结果可能对不上——使用时务必明确自己采用的算法。源码中的模式定义与实现原理在 MBQLMetabase Query Language层面week表达式对应:get-week子句。在 src/metabase/lib/schema/expression/temporal.cljc 中可以找到它的完整 schema 定义(mr/def ::week-mode [:enum {:decode/normalize common/normalize-keyword-lower} :iso :us :instance]) (mbql-clause/define-catn-mbql-clause :get-week :- :type/Integer [:datetime [:schema [:ref ::expression/temporal]]] [:mode [:? [:schema [:ref ::week-mode]]]])即week接受一个日期/时间表达式和一个可选的模式关键字iso/us/instance返回值类型为:type/Integer。同文件中还定义了日期提取单位:week-of-year-iso、:week-of-year-us、:week-of-year-instance等见 temporal.cljc 第 298~319 行附近。在后端 SQL 驱动中三种模式的计算逻辑集中在 src/metabase/driver/sql/query_processor.cljISO 模式:week-of-year-iso直接调用h2x/week即数据库原生周数函数。源码注释说明了 ISO 规则——第一周是包含第一个周四的那一周、周一起始若 1 月 1 日是周五则它属于上一年的最后一周。US 模式:week-of-year-us调用week-of-year辅助函数并把*start-of-week*强制绑定为:sunday因为 US 算法固定以周日为一周起始。Instance 模式:week-of-year-instance同样调用week-of-year辅助函数但*start-of-week*绑定为nil即跟随实例的start-of-week设置。week-of-year辅助函数的算法注释非常直白week-of-year 1 partial-week n full-weeks即「1 月 1 日永远在第 1 周部分周」从第一个start-of-week起始日开始的完整周依次递增计数完整周数由ceil((day-of-year - days-till-start-of-first-full-week) / 7)计算得出。这里的*start-of-week*动态变量定义在 src/metabase/driver/common.clj其注释明确说明它主要用于在 US 模式下覆盖周起始日为周日。这从实现层面印证了文档中「week(column)默认 ISO、US固定周日、Instance跟随本地化设置」的行为。而查询构建器默认分组:week-of-year走的是另一条实现路径——先把日期按周截断再计算ceil(day-of-year / 7)因此它始终以周日为起点且与上述三种模式均不相同与文档描述完全吻合。不同算法下首尾周的差异对比不同算法在跨年边界上的周数归属差异是实际使用时最容易踩坑的地方。下面按四种情况分别说明示意图均取自当前仓库文档查询构建器「Group by Week of year」默认算法——一年第一个周日所在周为第 1 周周日之前的日子属于上一年最后一周week(column)或week(column, ISO)——包含第一个周四的周为第 1 周周一为周起始week(column, US)——1 月 1 日所在周为第 1 周周日为周起始week(column, Instance)假设实例的一周起始日为周一——1 月 1 日所在周为第 1 周周起始跟随本地化设置从这几张示意图可以看出同一年 1 月 1 日至第一个周起始日之间的日期在不同算法下可能属于「上一年的最后一周」ISO/默认算法也可能属于「本年的第 1 周」US/Instance 算法而第一个完整周是否从 1 月 1 日之后的第一个周一或周日开始也直接决定了第 2 周的起点。在做跨年数据对比前建议先确认自己使用的算法避免周序号错位。不同数据库的 SQL 实现差异week表达式在最终执行时会翻译为各数据库自己的周数函数而不同 SQL 数据库提取「年周」的方式和算法并不一致有些引擎提供多个提取周数的函数有些则提供多种周数计算算法。具体请以你所使用数据库的官方文档为准。以下是各主流数据库提取年周的示例函数不完整列表数据库示例第一周算法PostgresEXTRACT(WEEK FROM TIMESTAMP created_at)ISO 算法MySQLWEEKOFYEAR(2017-06-15)一周起始日为周一且一年中第一周必须超过 3 天存在替代函数BigQueryEXTRACT(WEEK FROM DATE ticreated_at)周从 0 开始编号一年第一个周日之前的日期属于第 0 周存在替代函数RedshiftDATE_PART(week, TIMESTAMP created_at)ISO 算法这也能解释为什么 Metabase 在 src/metabase/driver/sql/query_processor.clj 中要「自研」week-of-year的计算逻辑源码注释写道 We have to roll our own to account for arbitrary start of week——因为各数据库原生周数函数的起始日与实例本地化设置未必一致Metabase 需要自行用日期算术来保证US/Instance模式的行为可预期。同时[sql :week-of-year-iso]直接复用h2x/week也说明当算法恰好与数据库原生函数一致时Metabase 会优先透传原生能力。实践建议小结做周同比对比优先使用查询构建器的「Week of year」分组但要注意其默认算法固定以周日为周起始、第一个周日所在周为第 1 周需要跟随实例本地化设置时用week([列], Instance)表达式提取周序号再基于结果创建自定义列用于分组或过滤涉及跨年数据时先通过上文示意图确认算法边界行为尤其是 1 月 1 日前后几天的周序号归属团队内应统一约定周数算法ISO 或 US否则同一报表在不同周起始日设置下可能得到不同结果使用原生 SQL 直连时注意各数据库周数函数的行为差异如 BigQuery 从 0 开始编号必要时在查询中显式指定算法。相关参考文件查询构建器使用说明见 editor.md本地化设置一周起始日见 localization.md表达式 schema 见 temporal.cljcSQL 计算实现见 query_processor.clj。【免费下载链接】metabaseThe easy-to-use open source Business Intelligence and Embedded Analytics tool that lets everyone work with data :bar_chart:项目地址: https://gitcode.com/GitHub_Trending/me/metabase创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考