快速提示: 时间 Dimension with 时间 Bands at Seconds Granularity in Power BI 和 SSAS表格

过去我也写过其他有关此主题的文章，您可以找到它们 这里 和 这里。在第一篇文章中，我解释了如何创建“Time”时间带的维度 分钟 粒度。然后，我的一位客户要求“Time”几秒钟内的尺寸，这鼓励了我写第二篇博客文章。在第二篇博文中，虽然我没有’t做时间带，所以我在这里写第三篇文章，这是第二篇文章支持5分钟，15分钟，30分钟，45分钟和60分钟的时间带的变体。“Time”尺寸降至秒。在这篇快速文章中，我直接跳到重点，向您展示如何生成“Time”维度以三种不同的方式，即在SQL Server中使用T-SQL，使用Power Query（M）和DAX。然后是这里：

时间 Dimension at Second Grain with 功率查询 (M) Supporting 时间 Bands:

在查询编辑器中复制/粘贴以下代码’s Advanced Editor to generate 时间 dimension in 功率查询:

let
Source = Table.FromList({1..86400}, Splitter.SplitByNothing()),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "ID"}}),
    #"时间 Column Added" = Table.AddColumn(#"Renamed Columns", "时间", each 时间.From(#datetime(1970,1,1,0,0,0) + #duration(0,0,0,[ID]))),
    #"Hour Added" = Table.AddColumn(#"时间 Column Added", "Hour", each 时间.Hour([Time])),
    #"Minute Added" = Table.AddColumn(#"Hour Added", "Minute", each 时间.Minute([Time])),
    #"5 Min Band Added" = Table.AddColumn(#"Minute Added", "5 Min Band", each 时间.From(#datetime(1970,1,1,0,0,0) + #duration(0, 0, Number.RoundDown(Time.Minute([Time])/5) * 5, 0))  +  #duration(0, 0, 5, 0)),
    #"15 Min Band Added" = Table.AddColumn(#"5 Min Band Added", "15 Min Band", each 时间.From(#datetime(1970,1,1,0,0,0) + #duration(0, 0, Number.RoundDown(Time.Minute([Time])/15) * 15, 0))  +  #duration(0, 0, 15, 0)),
    #"30 Min Band Added" = Table.AddColumn(#"15 Min Band Added", "30 Min Band", each 时间.From(#datetime(1970,1,1,0,0,0) + #duration(0, 0, Number.RoundDown(Time.Minute([Time])/30) * 30, 0))  +  #duration(0, 0, 30, 0)),
    #"45 Min Band Added" = Table.AddColumn(#"30 Min Band Added", "45 Min Band", each 时间.From(#datetime(1970,1,1,0,0,0) + #duration(0, 0, Number.RoundDown(Time.Minute([Time])/45) * 45, 0))  +  #duration(0, 0, 45, 0)),
    #"60 Min Band Added" = Table.AddColumn(#"45 Min Band Added", "60 Min Band", each 时间.From(#datetime(1970,1,1,0,0,0) + #duration(0, 0, Number.RoundDown(Time.Minute([Time])/60) * 60, 0))  +  #duration(0, 0, 60, 0)),
    #"Removed Other Columns" = Table.SelectColumns(#"60 Min Band Added",{"时间", "Hour", "Minute", "5 Min Band", "15 Min Band", "30 Min Band", "45 Min Band", "60 Min Band"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"时间", type time}, {"Hour", Int64.Type}, {"Minute", Int64.Type}, {"5 Min Band", type time}, {"15 Min Band", type time}, {"30 Min Band", type time}, {"45 Min Band", type time}, {"60 Min Band", type time}})
in
#"Changed Type"

时间 Dimension at Seconds Grain with 达克斯 Supporting 时间 Bands:

使用以下DAX表达式创建一个计算表：

时间 (DAX) = 
SELECTCOLUMNS(
    通用系列(1/86400, 1, TIME(0, 0, 1))
    , "时间", [Value]
    , "Hour", HOUR ( [Value] )
    , "Minute", MINUTE ( [Value] )
    , "5 Min Band",  TIME(HOUR([Value]), FLOOR(MINUTE([Value])/5, 1) * 5, 0) + TIME(0, 5, 0)
    , "15 Min Band", TIME(HOUR([Value]), FLOOR(MINUTE([Value])/15, 1) * 15, 0) + TIME(0, 15, 0)
    , "30 Min Band", TIME(HOUR([Value]), FLOOR(MINUTE([Value])/30, 1) * 30, 0) + TIME(0, 30, 0)
    , "45 Min Band", TIME(HOUR([Value]), FLOOR(MINUTE([Value])/45, 1) * 45, 0) + TIME(0, 45, 0)
    , "60 Min Band", TIME(HOUR([Value]), FLOOR(MINUTE([Value])/60, 1) * 60, 0) + TIME(0, 60, 0)
 )

时间 Dimension at Seconds Grain with T-SQL Supporting 时间 Bands:

在SSMS中运行以下T-SQL脚本会创建一个新的“Time”当前辽宁体育彩票库中的表。下面的代码没有’t检查现有的“Time” table though.

;WITH GenerateInt AS 
(SELECT 1 ID
UNION ALL
SELECT id + 1
FROM GenerateInt
WHERE id < 86400)
, GenerateTime AS
(
SELECT Dateadd(second, id, '1900-01-01') [Time]
FROM GenerateInt)
SELECT [Time]
	, DATEPART(HOUR, [Time]) [Hour]
	, DATEPART(MINUTE, [Time]) [Minute]
	, DATEADD(MINUTE, (((DATEPART(MINUTE, [Time]) / 5) * 5) + 5), '1900-01-01') [5 Minute Band]
	, DATEADD(MINUTE, (((DATEPART(MINUTE, [Time]) / 15) * 15) + 15), '1900-01-01') [15 Minute Band]
	, DATEADD(MINUTE, (((DATEPART(MINUTE, [Time]) / 30) * 30) + 30), '1900-01-01') [30 Minute Band]
	, DATEADD(MINUTE, (((DATEPART(MINUTE, [Time]) / 45) * 45) + 45), '1900-01-01') [45 Minute Band]
	, DATEADD(MINUTE, (((DATEPART(MINUTE, [Time]) / 60) * 60) + 60), '1900-01-01') [60 Minute Band]
INTO [Time]
FROM GenerateTime
OPTION (MAXRECURSION 0)

你觉得这篇文章有用吗？您是否曾经使用过这些方法中的任何一种来处理“Time”尺寸？你有更好的主意吗？请在下面的评论部分与我们分享。