数据库视图创建专家

1. 视图创建SQL语句

-- 视图名称：vw_hr_rd_weekly_attendance_90d
-- 作用：R&D在职员工近90天按周聚合工时与加班，供HR在权限控制下查询
-- 说明：
-- 1) 周起始日为周一（基于 MySQL WEEKDAY：周一=0 ... 周日=6）
-- 2) 只包含 R&D 且在职员工
-- 3) 仅输出指定字段，自动屏蔽手机号、薪资等敏感信息
-- 4) 视图内限制近90天，避免全表扫描

-- 注意：
-- - 假定 employee_attendance 表包含如下字段：
--   employee_id, name, dept, attendance_date, work_hours, overtime_hours, is_active
-- - 若在职标识不是 is_active，请将条件 ea.is_active = 1 替换为如
--   ea.employment_status = 'ACTIVE' 或 ea.termination_date IS NULL

CREATE OR REPLACE
ALGORITHM = TEMPTABLE
-- 请将以下 DEFINER 替换为受控的最小权限账户，例如仅有 SELECT 权限的专用账户
DEFINER = `hr_view`@`%`
SQL SECURITY DEFINER
VIEW `vw_hr_rd_weekly_attendance_90d` AS
SELECT
  ea.employee_id AS employee_id,
  ea.name        AS name,
  ea.dept        AS dept,
  DATE_SUB(DATE(ea.attendance_date), INTERVAL WEEKDAY(DATE(ea.attendance_date)) DAY) AS week_start,
  ROUND(SUM(COALESCE(ea.work_hours, 0)), 2)        AS work_hours,
  ROUND(SUM(COALESCE(ea.overtime_hours, 0)), 2)    AS overtime_hours
FROM
  `employee_attendance` ea
WHERE
  ea.dept = 'R&D'
  AND ea.is_active = 1
  AND ea.attendance_date >= CURRENT_DATE - INTERVAL 90 DAY
GROUP BY
  ea.employee_id,
  ea.name,
  ea.dept,
  DATE_SUB(DATE(ea.attendance_date), INTERVAL WEEKDAY(DATE(ea.attendance_date)) DAY);

权限授予（示例）：

-- 为 HR 角色或账号授予只读权限（仅示例，按贵司权限体系调整）
GRANT SELECT ON `vw_hr_rd_weekly_attendance_90d` TO 'hr_analyst'@'%';

2. 视图功能说明

• 业务目标：为HR提供“R&D部门在职员工近90天内每人每周的工时与加班统计”。
• 周定义：以周一为周起始日；week_start 为每周周一的日期。
• 数据范围：
  • 限定 dept = 'R&D'（在视图内强制过滤，避免绕过）
  • 限定在职员工（is_active = 1）
  • 限定近90天数据（视图内过滤，避免全表扫描）
• 安全性：仅输出 employee_id、name、dept、week_start、work_hours、overtime_hours，不包含手机号、薪资等敏感字段；通过视图授权给HR使用。
• 可过滤项：支持按 name 和 week_start 在视图外层查询进行过滤。

3. 字段映射关系表

• employee_id
  • 来源：employee_attendance.employee_id
  • 说明：员工唯一标识
• name
  • 来源：employee_attendance.name
  • 说明：员工姓名
• dept
  • 来源：employee_attendance.dept
  • 说明：部门（视图内部强制限定为 'R&D'）
• week_start
  • 来源：DATE_SUB(DATE(attendance_date), INTERVAL WEEKDAY(DATE(attendance_date)) DAY)
  • 说明：所属周的周一日期，作为周粒度的聚合键
• work_hours
  • 来源：SUM(COALESCE(employee_attendance.work_hours, 0))
  • 说明：该周合计标准工时（保留2位小数）
• overtime_hours
  • 来源：SUM(COALESCE(employee_attendance.overtime_hours, 0))
  • 说明：该周合计加班工时（保留2位小数）

过滤条件（视图内部）：

• dept = 'R&D'
• is_active = 1（如实际字段不同，请按实际替换）
• attendance_date >= CURRENT_DATE - INTERVAL 90 DAY

4. 使用示例

• 查询近90天所有R&D在职员工的每周工时与加班（默认结果已按周聚合）

SELECT *
FROM vw_hr_rd_weekly_attendance_90d;

• 按姓名精确过滤（示例：张三）

SELECT *
FROM vw_hr_rd_weekly_attendance_90d
WHERE name = '张三';

• 按姓名模糊过滤（示例：姓名包含“张”）

SELECT *
FROM vw_hr_rd_weekly_attendance_90d
WHERE name LIKE '%张%';

• 按周起始日过滤（示例：查询 2025-11-24 这一周的数据，周一为起始）

SELECT *
FROM vw_hr_rd_weekly_attendance_90d
WHERE week_start = '2025-11-24';

• 同时按姓名与周起始日过滤

SELECT *
FROM vw_hr_rd_weekly_attendance_90d
WHERE name = '李四'
  AND week_start = '2025-11-24';

• 按起止周范围过滤（示例：近4周）

SELECT *
FROM vw_hr_rd_weekly_attendance_90d
WHERE week_start BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 28 DAY)
                     AND CURRENT_DATE;

提示：视图中已限制近90天数据，如需更长窗口，请调整视图并评估性能。

5. 性能优化建议

必做优化：

• 索引建议（在 employee_attendance 表上）
  • 建议建立复合索引，覆盖过滤与分组关键列：
    • (dept, is_active, attendance_date, employee_id)
  • 若已有 employee_id, attendance_date 等索引，确保 attendance_date 在复合索引中位置尽量靠前以优化 90 天时间窗过滤。
• 字段类型与空值
  • 确保 attendance_date 为 DATE 或 DATETIME 并对 work_hours、overtime_hours 采用定点数（如 DECIMAL(6,2)）。
  • work_hours 与 overtime_hours 允许 NULL 时，视图已使用 COALESCE 规避聚合空值影响。

可选优化（按数据量评估后实施）：

• 生成列与索引（MySQL 8.0+）
  • 为周起始日创建持久化生成列并加索引，提升对周粒度筛选与聚合的性能：

    ALTER TABLE employee_attendance
      ADD COLUMN week_start DATE
        GENERATED ALWAYS AS (DATE_SUB(DATE(attendance_date), INTERVAL WEEKDAY(DATE(attendance_date)) DAY)) STORED,
      ADD INDEX idx_attendance_week (dept, is_active, week_start, employee_id);
    

  • 视图中可改用 ea.week_start 以避免重复计算表达式。
• 物化汇总表
  • 若 attendance 数据量大且报表频繁，可将“周聚合结果”每日增量刷新到汇总表，再对汇总表建视图供查询。此方案需配合定时任务与数据校验，实施前请在测试环境评估。

安全与治理：

• 使用最小权限原则
  • 建议使用专用 DEFINED 账户（仅 SELECT 基表所需字段），并仅将视图的 SELECT 授予 HR 角色/用户。
• 字段白名单
  • 严格通过视图只暴露必要字段；如基表新增敏感列，不影响视图输出与权限边界。

维护建议：

• 版本控制视图SQL
  • 将视图创建脚本纳入版本管理，变更前在测试环境验证正确性与性能。
• 周定义一致性
  • 若企业存在不同周起始定义，请统一规范（本视图采用周一为周起始），避免口径不一致。

如实际表结构与本文假设存在差异（尤其是在职标识字段名、日期字段名），请告知具体字段名称，我将据实调整视图SQL与索引方案。

以下方案基于SQL Server，面向“事件数据接口”的近7天客户行为明细视图，兼顾查询性能、数据安全与可维护性。为确保一致性与安全性，视图仅输出必要业务字段，并对IP与UA进行不可逆或弱化可识别度的掩码处理，同时过滤爬虫与内测设备数据。

前置假设（请与实际表结构校验并据此微调）

• 基表：dbo.customer_event_log
• 关键字段：
  • 事件主键字段：event_time datetime2，event_name，user_id_hash，session_id
  • 渠道维度：platform，source，utm_campaign，country，city
  • 安全字段：ip_address nvarchar(64)，user_agent nvarchar(512)
  • 过滤标识：is_bot bit，is_internal_device bit，device_env nvarchar(20)（可取 test/internal/staging/qa 等）
• 若字段名不一致，请在视图中对齐替换

1. 视图创建SQL语句

-- 视图：近7天行为明细（已过滤bot/内测，IP/UA已掩码）
-- 目标用途：数据接口使用；按 event_time 可筛选
-- 注意：如需调整过滤策略或掩码规则，请在测试环境验证后再变更生产

IF OBJECT_ID('dbo.v_customer_event_7d_api', 'V') IS NOT NULL
    DROP VIEW dbo.v_customer_event_7d_api;
GO

CREATE VIEW dbo.v_customer_event_7d_api
AS
SELECT
    cel.event_time,
    CAST(cel.event_time AS date) AS event_date,  -- 基于UTC的日期衍生，如有本地时区需求请另行转换
    cel.user_id_hash,
    cel.session_id,
    cel.event_name,
    cel.platform,
    cel.source,
    cel.utm_campaign,
    cel.country,
    cel.city,

    -- IP掩码：IPv4降至/24；非IPv4（含端口/IPv6/异常）回退为哈希后节选
    CASE 
        WHEN cel.ip_address LIKE '[0-9]%.[0-9]%.[0-9]%.[0-9]%' 
             AND PARSENAME(cel.ip_address,4) IS NOT NULL 
             AND PARSENAME(cel.ip_address,3) IS NOT NULL 
             AND PARSENAME(cel.ip_address,2) IS NOT NULL 
             AND PARSENAME(cel.ip_address,1) IS NOT NULL
        THEN CONCAT(PARSENAME(cel.ip_address,4), '.', PARSENAME(cel.ip_address,3), '.', PARSENAME(cel.ip_address,2), '.0')
        WHEN cel.ip_address IS NOT NULL
        THEN CONCAT('h_', RIGHT(CONVERT(varchar(64), HASHBYTES('SHA2_256', cel.ip_address), 2), 16))
        ELSE NULL
    END AS ip_masked,

    -- UA掩码：保留前缀+哈希后8字节节选（不泄露完整UA）
    CASE 
        WHEN cel.user_agent IS NULL THEN NULL
        ELSE CONCAT(LEFT(cel.user_agent, 16), N'…', RIGHT(CONVERT(varchar(64), HASHBYTES('SHA2_256', cel.user_agent), 2), 8))
    END AS user_agent_masked

FROM dbo.customer_event_log AS cel
WHERE 
    -- 仅近7天（UTC），区间可利用索引(event_time)做SEEK
    cel.event_time >= DATEADD(DAY, -7, SYSUTCDATETIME())
    AND cel.event_time <  SYSUTCDATETIME()

    -- 过滤bot与内测设备（优先使用标识列；UA关键词仅作为兜底）
    AND NOT (
        COALESCE(cel.is_bot, 0) = 1
        OR COALESCE(cel.is_internal_device, 0) = 1
        OR COALESCE(cel.device_env, '') IN ('test', 'internal', 'staging', 'qa')
        OR COALESCE(cel.source, '') IN ('bot', 'crawler', 'monitoring')
        OR (cel.user_agent IS NOT NULL AND (
            cel.user_agent LIKE '%bot%' OR cel.user_agent LIKE '%crawler%' OR cel.user_agent LIKE '%spider%' OR cel.user_agent LIKE '%headless%'
        ))
    );
GO

2. 视图功能说明

• 提供近7天的客户行为明细数据，支持下游数据接口直接查询
• 安全处理：
  • 不输出原始IP与User-Agent，仅输出掩码字段：ip_masked、user_agent_masked
  • 过滤常见爬虫与内测设备数据，优先依据显式标识（is_bot、is_internal_device、device_env、source），UA关键词为兜底
• 可按event_time进行进一步筛选（视图已限制近7天；下游可在此基础上加更窄的时间范围）
• 结果字段聚焦业务需求，便于接口稳定使用与后续维护

3. 字段映射关系表

• event_time <- customer_event_log.event_time（原样透出）
• event_date <- CAST(customer_event_log.event_time AS date)（由事件时间派生）
• user_id_hash <- customer_event_log.user_id_hash（原样透出，已为哈希）
• session_id <- customer_event_log.session_id（原样透出）
• event_name <- customer_event_log.event_name（原样透出）
• platform <- customer_event_log.platform（原样透出）
• source <- customer_event_log.source（原样透出）
• utm_campaign <- customer_event_log.utm_campaign（原样透出）
• country <- customer_event_log.country（原样透出）
• city <- customer_event_log.city（原样透出）
• ip_masked <- 掩码逻辑：
  • 若IPv4：降至/24网段（x.y.z.0）
  • 否则：SHA2_256哈希后取后16位十六进制并加前缀h_
• user_agent_masked <- 掩码逻辑：保留前16字符 + SHA2_256哈希后8字节节选（共24字符左右）

4. 使用示例

• 获取最新100条事件

SELECT TOP (100) *
FROM dbo.v_customer_event_7d_api
ORDER BY event_time DESC;

• 按时间范围进一步筛选（在近7天内收窄到24小时）

DECLARE @start_utc datetime2 = '2025-12-02T00:00:00Z';
DECLARE @end_utc   datetime2 = '2025-12-03T00:00:00Z';

SELECT *
FROM dbo.v_customer_event_7d_api
WHERE event_time >= @start_utc
  AND event_time <  @end_utc
ORDER BY event_time;

• 按平台与国家聚焦查询

SELECT event_time, user_id_hash, event_name, platform, country
FROM dbo.v_customer_event_7d_api
WHERE platform = 'iOS'
  AND country = 'US'
  AND event_time >= DATEADD(HOUR, -12, SYSUTCDATETIME())
ORDER BY event_time DESC;

5. 性能优化建议

• 索引与数据分布
  • 确保基表存在以 event_time 为首列的覆盖索引，便于时间范围查询与排序
    • 建议评估（请先在测试环境验证其收益与写入成本）：
      • 候选索引A（通用覆盖，减少Lookup）： CREATE INDEX IX_customer_event_log_time_cover ON dbo.customer_event_log (event_time) INCLUDE (user_id_hash, session_id, event_name, platform, source, utm_campaign, country, city, is_bot, is_internal_device, device_env, user_agent, ip_address);
      • 若 user_agent 或 ip_address 列较宽且访问量大，可考虑将掩码值预计算为持久化计算列（见下），再将预计算列放入 INCLUDE，以降低在线HASHBYTES开销
  • 大表建议按时间进行分区（如按event_date分区），以便近7天查询主要命中热分区
• 过滤策略优化
  • 优先依赖标识列（is_bot、is_internal_device、device_env、source）进行过滤，并对其建立低基数辅助索引或将其放入覆盖索引的键或包含列中
  • UA关键词匹配属于兜底逻辑，可能带来CPU负担。可在基表上新增持久化计算列并索引，以提升过滤效率：
    • 例如： ALTER TABLE dbo.customer_event_log ADD is_bot_ua AS (CASE WHEN user_agent IS NOT NULL AND (user_agent LIKE '%bot%' OR user_agent LIKE '%crawler%' OR user_agent LIKE '%spider%' OR user_agent LIKE '%headless%') THEN 1 ELSE 0 END) PERSISTED; -- 并将 is_bot_ua 纳入过滤条件替代UA LIKE（视图相应修改）
• 掩码开销控制
  • HASHBYTES 对高QPS接口会带来CPU开销。若接口访问量较高，建议：
    • 在基表新增持久化计算列：
      • ip_masked_persisted（按上述规则实现）
      • ua_masked_persisted（按上述规则实现）
    • 视图直接选择预计算列，避免在线哈希
• 统计信息与并发
  • 保持 event_time 与过滤列的统计信息新鲜（启用自动更新统计信息并定期维护），确保优化器做出正确的执行计划
  • 对高并发接口查询，建议固定选择必要字段，避免 SELECT *，减少行宽与IO
• 注意事项
  • 视图使用 SYSUTCDATETIME() 作为“近7天”的上、下界，因此是滚动时间窗口；该写法仍可利用 event_time 上的索引进行范围查找
  • 如需可复用的任意时间窗查询，建议额外提供参数化的内联表值函数（iTVF），作为扩展接口供分析类任务使用

安全与合规声明

• 视图不直接暴露原始IP与User-Agent，且对UA仅输出前缀+哈希节选，对IP优先输出/24网段或哈希节选，降低个体可识别风险
• 请根据公司隐私政策评估掩码强度是否满足合规要求（如需更强匿名化，可将IPv4亦全部改为哈希节选）