如何最好地存储用户数据与日期/时间维度？用例是我试图存储每天、每小时的用户操作。例如分享数、喜欢数、好友数等。我有一个时间表和日期表。对于时间来说很简单 - 我每天的每个小时的每一行 = user_id 和列 = 1 到 24 。但问题在于日期。如果我每天= 1 列，那么我一年将有 365 列。我也无法存档数据，因为分析也需要过去的数据。还有哪些其他策略？
dimDate : 1 row per date
dimTime : 1 row per minute
一开始你必须声明“grain事实表的“然后坚持下去.
如果一粒粮食是一日，那么TimeKey总是指向“23:59”这个键。
如果谷物是一小时，那么TimeKey指向“HH:59”的条目。
如果谷物是一分钟，那么TimeKey指向相应的“HH:MM”
如果谷物是15分钟，那么TimeKey指向相应的“HH:14”、“HH:29”、“HH:44”、“HH:59”
等等...
-- How many new friends did specific user gain
-- in first three months of years 2008, 2009 and 2010
-- between hours 3 and 5 in the morning
-- by day of week
-- not counting holidays ?
select
DayOfWeek
, sum(NewFriends) as FriendCount
from factUserAction as f
join dbo.dimUser as u on u.UserKey = f.UserKey
join dbo.dimDate as d on d.DateKey = f.DateKey
join dbo.dimTime as t on t.TimeKey = f.TimeKey
where CalendarYear between 2008 and 2010
and MonthNumberInYear between 1 and 3
and t.Hour between 3 and 5
and d.IsHoliday = 'no'
and UserEmail = '[email protected]'
group by DayOfWeek
order by DayOfWeek ;