正文

查询涉及时间维度的数仓数据

 2023-03-29  51

关键词：

【中文标题】查询涉及时间维度的数仓数据【英文标题】：Querying a data warehouse data involving time dimension 【发布时间】：2011-08-14 11:26:50 【问题描述】：

我有两个时间维度表

日期（每一天的唯一行）一天中的时间（一天中每一分钟的唯一行）

给定这个模式，如果一个人想要检索过去 X 小时的事实，其中 X 可以是任何大于 0 的数字，查询会是什么样子。

当开始时间和结束时间恰好在一年中的两个不同日子时，事情就开始变得棘手了。

编辑：我的事实表没有时间戳列

【问题讨论】：

您可能会创建一个时间列（使用视图，或“with”，或仅与子查询连接）然后使用它。但是时间命令变化很大，知道你正在使用什么数据库是至关重要的。我目前正在使用 mysql，但我必须在不久的将来使其至少与 sqlserver、oracle 和 sqllite 一起使用 【参考方案1】：

事实表确实具有（并且应该具有）原始时间戳，以避免在一天的边界上发生奇怪的按时间查询。奇怪意味着在 WHERE 子句中有某种复杂的日期时间函数。

在大多数 DW 中，这类查询非常少见，但您似乎将数据流式传输到 DW 并同时使用它进行报告。

所以我建议：

在事实表中引入完整的时间戳。

对于旧记录，从日期和时间键重新创建时间戳。

DW 查询都是关于在 WHERE 子句中没有任何函数，或者如果必须使用函数，请确保它是 SARGABLE。

【讨论】：

【参考方案2】：

将Start Date 和End Date 列转换为TIMESTAMP 并填充它们可能会更好。

切片表需要使用适当的interval BETWEEN Start Date AND End Date。在 Oracle 中，interval 类似于SYSDATE - (4/24) 或SYSDATE - NUMTODSINTERVAL(4, 'HOUR')

这也可以改写为：

Start Date <= (SYSDATE - (4/24)) AND End Date >= (SYSDATE - (4/24))

【讨论】：

【参考方案3】：

在我看来，鉴于您拥有的当前架构，您需要从时间维度表中检索符合您的搜索条件的适当时间 ID，然后在事实表中搜索匹配的行。根据您的时间维度的粒度，您可能需要检查执行以下任一操作的性能（SQL Server 示例）：

子选择：

SELECT X FOO WHERE TIMEID IN (SELECT ID FROM DIMTIME WHERE HOUR >= DATEPART(HOUR, CURRENT_TIMESTAMP()) 和 DATEID IN (SELECT ID FROM DIMDATE WHERE DATE = GETDATE())

内连接：

SELECT X FOO INNER JOIN DIMTIME ON TIMEID = DIMTIME.ID WHERE HOUR >= DATEPART(HOUR, CURRENT_TIMESTAMP()) INNER JOIN DIMDATE ON DATEID = DIMDATE.ID WHERE DATE = GETDATE()

这些都不是真正有吸引力的选择。

您是否考虑过查询的多维数据集可能用于汇总分析，而不一定用于“最后 X”分析？

如果这不是“汇总”多维数据集，我会同意其他海报的观点，即您应该使用更好的键重新标记事实表，并且如果您确实打算经常搜索下班时间，您可能也应该将其包含在事实表中，因为任何其他尝试都可能使查询不可解析（请参阅What makes a SQL statement sargable?）。

Microsoft 在http://msdn.microsoft.com/en-us/library/aa902672%28v=sql.80%29.aspx 建议：

与其他维度表中使用的代理键相比，日期和时间维度键应该是“智能的”。日期维度的建议键采用“yyyymmdd”形式。这种格式很容易让用户记住并合并到查询中。对于按日期划分为多个表的事实表，它也是推荐的代理键格式。

祝你好运！

【讨论】：

大数据开发工程师需要了解的数仓中的维度设计(代码片段)

...何分层（2）企业数仓模型分层架构（3）维度设计如何理解（4）维表是怎么生成的（5）维度整合的两种策略（6）维度拆分的最佳方案（7）缓慢变化维的处理方式（8）企业中... 查看详情

大数据开发工程师需要了解的数仓中的维度设计(代码片段)

数仓初识-理论知识

...宽表设计，对DWD层的数据结构进行反范式设计，减少数仓查询的Join操作ADS层也称之为数据集市层，是为了屏蔽业务直接查询数仓从而导致数仓压力增大的问题，所以该层会使用很多组件如Kylin、ES等组件加速数仓查询速度。OLTP为... 查看详情

数仓第二篇：数据模型（维度建模）(代码片段)

...何更快速完成分析需求，同时还有较好的大规模复杂查询的响应性能。它是面向分析的，为了提高查询性能可以增加数据冗余，反规范化的设计技术。1.1事实表事实表产生于业务过程，存储了业务活动或事件提炼... 查看详情

你应该知道的数仓安全(代码片段)

...》，原文作者：zhangkunhn。前言最近遇到一个客户场景，涉及共享schema的权限问题。场景简单可以描述为：一些用户是数据的生产方，需要在schema中创建表并写入数据；另查看详情

基于阿里（onedata）的数仓体系建设(代码片段)

...据调研三、架构设计四、指标体系搭建五、模型设计六、维度设计七、事实表设计八、其他规范OneData是阿里巴巴内部进行数据整合和管理方法体系和工具。一、指导思想首先，要进行充分的业务调研和需求分析。其次，... 查看详情

基于阿里（onedata）的数仓体系建设

...据调研三、架构设计四、指标体系搭建五、模型设计六、维度设计七、事实表设计八、其他规范OneData是阿里巴巴内部进行数据整合和管理方法体系和工具，其方法论对数仓建模依然有借鉴之处，一起来温故下来自《大数... 查看详情

基于阿里（onedata）的数仓体系建设

聊聊数据仓库中的缓慢变化维度（scd）

...维度的变更（changetracking），以保留历史，并提供准确的查询和分析结果。在《TheDataWarehouseToolkit,3rdEdition》一书的第5章，Kimball提出了多种缓慢变化维度的类型和处理方法，其中前五种是原生的，后面的方法都是混合方法（hybridt... 查看详情

说说数仓(6)-关于命名规范

...发挥，后期想要统一或者重构，会浪费很大的人力成本和时间成本，记住，这都是坑。这里以我目前公司的一些经验，分享下。常规来说，数仓的建设是按照数仓分层模型开发的。也有会按照业务线来分层，在各自业务线下重新... 查看详情

本地数仓项目(四）——即席查询(代码片段)

1背景本文描述本地数仓项目即席查询相关内容，主要涉及即席查询工具包括Presto、Druid、Kylin。本文基于文章《本地数据仓库项目(一)——本地数仓搭建详细流程》和《本地数仓项目(二)——搭建系统业务数仓详细流程》以及... 查看详情

数仓第二篇：数据模型（维度建模）(代码片段)

目录本文导航前言：01.基本概念1.1事实表1.2维度表1.3粒度1.4维度建模流程02.建模方法--经典数据仓库模型2.1维度模型2.2范式模型2.3DataVault模型2.4Anchor模型03.建模工具3.1PowerDesigner3.2ERWin3.3Visio3.4ExcelMapping04.结语本文导航前言࿱... 查看详情

如何设计好数仓？数仓到底是什么？---详解

....3、ETL4.4、数据仓库与数据集市4.5、应用系统4.6、用户5、维度分析5.1、维度分析介绍5.2、指标与维度5.3、维度分层与分级5.4、下钻与上卷6、数仓建模6.1、事实表6.1.1、分类6 查看详情

数仓第四篇:名词解析及关系

目录一、数仓中常见概念解析1.实体2.维度3.度量4.粒度5.口径6.指标7.标签8.自然键9.持久键10.代理键11.退化维度12.缓慢变化维13.微型维度14.下钻15.上卷16.数据集市二、数仓概念之间关系1.实体表，事实表，维度表之间的关系... 查看详情

数仓第四篇:名词解析及关系

大数据面试题--数仓

...据仓库理论数据分层ETL星形模型与雪花模型的区别？维度建模（dimensionalmodeling）：数据仓库项目最重要或需要注意的是什么，以及如何处理？关系建模与维度建模拉链表：维度表和事实表数据仓库中的... 查看详情

数仓实践：浅谈kimball维度建模

我们不管是基于Hadoop的数据仓库（如Hive），还是基于传统MPP架构的数据仓库（如Teradata），抑或是基于传统Oracle、MySQL、SQLServer关系型数据库的数据仓库，其实都面临如下问题：怎么组织数据仓库中... 查看详情