时区、币种、汇率怎么处理？——出海业务的指标坑

-- 错误做法：直接按UTC日期
SELECT DATE(login_time_utc) AS dt, COUNT(DISTINCT user_id) AS dau
FROM ods_login WHERE login_time_utc >= '2023-03-12'
GROUP BY DATE(login_time_utc);
-- 问题：3月12日是美国DST切换日，UTC日期下美国用户的「当地一天」错位
 
-- 正确做法：按用户当地时区算「当地日期」
SELECT user_local_date AS dt, COUNT(DISTINCT user_id) AS dau
FROM (
  SELECT user_id,
         DATE(CONVERT_TZ(login_time_utc, 'UTC', tz.timezone))
           AS user_local_date
  FROM ods_login l
  LEFT JOIN dim_country_timezone tz ON l.country = tz.country_code
) t GROUP BY user_local_date;

-- 订单事实表的金额字段设计
CREATE TABLE dwd_order_pay (
  order_id          BIGINT,
  -- 原始本币（永远不动）
  pay_amount_local  DECIMAL(18,2),
  currency          VARCHAR(8),       -- USD/EUR/JPY/INR...
  -- USD折算（用支付当日汇率）
  pay_amount_usd    DECIMAL(18,2),
  fx_rate_to_usd    DECIMAL(18,6),    -- 用了哪个汇率
  fx_rate_date      DATE,             -- 用的哪天的汇率
  -- 报告币种（如人民币）
  pay_amount_cny    DECIMAL(18,2),
  fx_rate_to_cny    DECIMAL(18,6),
  pay_time_utc      TIMESTAMP
) PARTITIONED BY (dt);

3个币种独立字段+ ETL时锁定汇率，避免下游每次自己折算。fx_rate_date字段可以让审计很容易回溯"这条订单当时用了哪天的汇率"。

方法三：汇率快照表的设计

汇率本身就是一张表。要点：每日全量、不可变、来源明确：

CREATE TABLE dim_fx_rate_daily (
  fx_date         DATE,                     -- 汇率日期
  from_currency   VARCHAR(8),
  to_currency     VARCHAR(8),
  rate            DECIMAL(18,6),
  source          VARCHAR(32),              -- ECB/Open Exchange Rates
  source_time_utc TIMESTAMP,
  is_locked       BOOLEAN DEFAULT TRUE,    -- 锁定后不可改
  PRIMARY KEY (fx_date, from_currency, to_currency)
);
 
-- 三种汇率取法（按业务场景选）
-- 1. 当日中间价（订单实付折算用）
SELECT rate FROM dim_fx_rate_daily
WHERE fx_date = '2023-03-12' AND from_currency='EUR' AND to_currency='USD';
 
-- 2. 当月平均（财务月报用）
SELECT AVG(rate) FROM dim_fx_rate_daily
WHERE fx_date BETWEEN '2023-03-01' AND '2023-03-31'
  AND from_currency='EUR' AND to_currency='USD';
 
-- 3. 锁定汇率（年度预算用，由CFO指定）
SELECT 1.08 AS budget_rate;  -- 写死，对应整年预算

方法四：跨时区聚合的「全球vs本地」双视角

有些指标需要「全球视角」（按UTC日聚合），有些需要「本地视角」（按用户当地日）。我建议两个版本都跑出来：

-- 全球视角：按UTC日，全公司一个DAU（用于对外披露）
SELECT DATE(event_time_utc) AS utc_date,
       COUNT(DISTINCT user_id) AS dau_global
FROM dws_user_action_1d GROUP BY DATE(event_time_utc);
 
-- 本地视角：按用户当地日，看每个国家的真实DAU
SELECT event_dt_local AS local_date,
       country,
       COUNT(DISTINCT user_id) AS dau_local
FROM dws_user_action_1d GROUP BY event_dt_local, country;
 
-- 这两个DAU不会相等！全球视角=本地视角SUM-跨日重复

两个视角都对，但用途不同。在出海硬件品牌，对外披露用全球视角（一致性好），运营分析用本地视角（更真实反映用户行为）。

面试高频追问

追问一：DST切换日的用户行为怎么处理？

DST切换是出海业务最隐蔽的坑。3月那天"消失了1小时"（2:00直接跳到3:00），11月那天"多出1小时"（1:00-2:00跑两次）。处理原则：①事件时间用UTC存储（UTC不受DST影响）②聚合到本地日期时用Olson时区数据库（如America/Los_Angeles，自动处理DST），不要用纯offset（-08:00或-07:00）③DST切换日的小时级数据加`is_dst_transition`标记。我在出海硬件品牌DST那两天做了人工核对，平均偏差在0.3%以内是可接受的。

追问二：业务突然要看「按总部时区的DAU」，怎么快速实现？

如果按方法一存了3个时间字段+时区，30秒能写出来——只要再CONVERT_TZ一次到总部时区即可。如果只存了UTC，要重写ETL，时间长。这就是为什么要在事实表设计阶段就把时区维度独立——「设计一次、用一辈子」。我在出海硬件品牌为CEO办公室专门加过一个"按SF总部时区的DAU"，5分钟搞定，他很满意。

追问三：年度GMV用什么汇率？

看用途：①对内经营分析——用每月平均汇率，月内一致 ②对外披露——按GAAP/IFRS要求，一般用月平均或日均累加 ③年度预算/KPI——CFO锁定汇率，整年不变。三种各有道理，关键是字段独立、口径文档化。出海硬件品牌的GMV报表会有3个版本：current_fx（实时汇率）、monthly_avg_fx（月均）、locked_fx（预算锁定），一目了然。

实战踩坑：印度卢比汇率10%日波动导致GMV趋势误判

某出海硬件品牌2022年印度卢比一周贬值7%，我们用「订单当日汇率」折算USD时，印度GMV看起来环比下降12%——CEO以为是印度市场出问题，差点砍预算。其实印度本币GMV是涨的，只是汇率波动导致USD口径下降。后来我们做了三件事：①对外报表同时展示本币、USD、CNY三个版本 ②高波动地区增加「汇率影响」分析项 ③CEO周报固定加一句「剔除汇率影响后...」

教训：出海企业的财务指标必须区分"业务变化"和"汇率变化"。不分清就是给CEO误导。这种细节体现的是数据团队的业务理解深度，不是技术问题。

总结

回答这道题的高分结构：先点3大坑（时区/币种/汇率）→ 给设计（3个时间字段+3个币种字段+汇率快照表）→ 给业务洞察（DST处理、汇率影响分析、双视角DAU）。强调"出海指标治理是设计驱动的，事实表多设计几个字段省后期无数返工"。能聊到DST、Olson时区库、IFRS报告币种就是Senior+。

下一篇聊偏架构的话题：维度爆炸了——一个指标拆8个维度查，OLAP撑不住——指标和维度怎么共建？

你们做出海业务踩过最坑的时区/币种问题是什么？评论区聊聊～

关注「Wood聊大数据」| 9年大数据老兵，聊技术、聊职场、聊成长