在SQL中生成真实的用户时间戳（详细指南）

这是三部分系列中的第二部分，展示了我们如何生成有趣的假数据来演示Narrator。第一部分描述了如何创建一个数字表。

生成时间序列数据对于测试、调试和演示非常有用。例如，每当我们演示Narrator的数据平台时，我们都会展示从一个假公司生成的数据。这些数据都是通过在特定的（随机的）时间创建用户事件产生的。创建真实的时间戳是这个过程的核心。

不幸的是，在SQL中创建现实的合成时间戳是非常不直观的。Postgres有一个巧妙的 [generate_series()](https://dataschool.com/learn-sql/generate-series/)函数可以创建时间戳，但它会均匀地创建它们（这很有用，但不是我们想要的）。

这里我们将展示我们如何创建遵循合理使用模式的时间戳。我们将从一个简单的案例开始，一点一点地建立起来。

下面的代码将全部用于Redshift。它应该可以直接转换到任何其他仓库。

基本过程是选择一个目标开始和结束日期，所需的时间戳数量，并使用数字表来选择正确的行数。从那里，一个小的数学将在该区间创建具有我们想要的属性的时间戳。

随机时间戳

第一步是在我们的时间区间内均匀地创建时间戳。

-- generates timestamps randomly between the start and end times
with
random_timestamps as (
    select
        -- some 'variables' just to make things clear
        '2021-01-01'::timestamp as time_start,
        sysdate as time_end,
        date_diff('minute', time_start, time_end) as time_span_minutes,

        -- pick a random number (0..1) and multiply by total number of minutes
        -- offset that from the start time to get a random time stamp in the chosen interval
        date_add('minute',(time_span_minutes * random())::integer, time_start) as ts
    from dw_demo.numbers n 
    where n.number < time_span_minutes / 2  -- one timestamp every two minutes
)

select
    date_trunc('day', ts) AS day,
    count(1) as total_timestamps
from random_timestamps
group by day
order by day asc

上面的代码并不坏。它以每分钟为单位创建时间戳。这些时间戳是随机的，所以没有真正明显的模式。

好吧，有一个明显的模式--坡度是完美的直线。这不是超级现实的。让我们试着对使用量随时间增长进行建模。

增加的时间戳

让我们假设我们的时间戳代表用户活动--比如说网站会话。随着时间的推移，使用量会增加，所以我们应该让我们的合成时间戳遵循这个规律。

指数函数是一个很好的选择，因为它可以衡量复合增长。它的形式通常是

为了简单起见，我们将使用f（x）=rx的形式。例如，2x看起来像这样。

以2为底的指数函数

这就是所有初创公司希望看到的曲棍球式增长的模型，实际上是对实际用户增长的一个相当好的代表。

-- Generates timestamps that follow a function: y = r^x 
with
increasing_timestamps as (
    select
        '2020-01-01'::timestamp as time_start,
        sysdate as time_end,
        date_diff('minute', time_start, time_end) as time_span_minutes,

        random() as x,  -- generate a random value from 0 to 1 for x
        2^x - 1 as y,   -- get our y, in this case also between 0 and 1 for simplicity

        -- Note that when we're creating the timestamp for each row we're starting at time_end
        -- and working backwords -- effectively flipping y on the x axis
        -- We do this because we want the distribution of timestamps to look like our exponential function:
        -- fewer sessions in the beginning and more towards the end
        -- Think of y as the distance the timestamp is from the start time. Because our function is exponential,
        -- bigger y values are futher and further apart from each other. This means the distance between timestamps
        -- increases with bigger y. Flipping it gets us what we want: fewer timestamps at the start and more at the end
  
        date_add('minute',  -(time_span_minutes * y)::integer, time_end) as ts
    from dw_demo.numbers n 
    where n.number < time_span_minutes / 2  -- one timestamp every two minutes
)

select
    date_trunc('day', ts)::date AS day,
    count(1) as total_timestamps
from increasing_timestamps
group by day
order by day asc

因此，问题是如何生成遵循函数的时间戳。这样做的代码并不太疯狂。

它看起来像这样:

那么，发生了什么？

首先，我们使用函数2x- 1为0到1之间的x生成一个0到1之间的y值。我们将在下一节中处理不在0和2之间的值

所以我们有random() as x 和2^x - 1 as y

与之前的主要区别是，我们将使用y ，而不是x ，来创建实际的时间戳值。

我们还从时间段的结束处而不是开始处偏移y。这是因为对于早期的日期，y的值更接近于彼此（曲线的较平坦部分）。这意味着在给定的时间跨度内（比如说一天），有更多的人，使曲线呈下降趋势。以这种方式否定y，基本上就可以围绕y轴反射图形，解决了这个问题。

调整输出

这看起来不错，但如果我们想更仔细地控制我们正在使用的函数呢？例如，让我们把图形变得更陡峭。要做到这一点，我们只需要改变函数。

对于一个指数函数，如果我们给增长率一个更高的数字，我们将得到一个更陡峭的曲线：从f（x）=2x到f（x）=5x。相当简单明了。

2x（红色）和5x（蓝色）的曲线图

x 在上一节中，我们精心设计了我们的函数，使所有从0...1开始的y ，也在0...1之间。这次不可能进行这种简化，所以我们将有一个额外的步骤，将y 向下扩展。

这里是

-- Generates timestamps that follow a function: y = r^x 
with
quickly_increasing_timestamps as (
    select
        '2020-01-01'::timestamp as time_start,
        sysdate as time_end,
        date_diff('minute', time_start, time_end) as time_span_minutes,

        random() as x,  -- generate a random value from 0 to 1 for x
        5 as r,
        r^x as y,

        r^0 as y_start,
        r^1 as y_end,
        -- need to linearly scale y to between 0 and 1 to keep things simple
        -- at x = 0 y is 1 and at x = 1 y is 5
        -- subtract 1 to get between 0 and 4, then divide by 4 to get between 0 and 1
        (y-y_start)/(y_end - y_start) as scaled_y,

        date_add('minute',  -(time_span_minutes * scaled_y)::integer, time_end) as ts
    from dw_demo.numbers n 
    where n.number < time_span_minutes / 2  -- one timestamp every two minutes
)

select
    date_trunc('day', ts)::date AS day,
    count(1) as total_timestamps
from quickly_increasing_timestamps
group by day
order by day asc

代码几乎是一样的。我们只需要对y 做一个简单的线性插值，让它在0和1之间。

我们可以很容易地看到，现在的图表更加陡峭，而且与以前相比，早期的时间戳更少，后期的时间戳更多。

增量创建

这在创建一整套时间戳方面是很好的，但如果你想一次创建一点时间戳呢？我们的演示账户就是这样做的--每天我们根据这些时间戳添加新的用户事件，以使演示看起来是活的，是最新的。

在上面的例子中，我们在过去的某个时间和现在之间生成了时间戳。这对于增量生成来说是行不通的，因为随后的日子不会排在一起。选择24小时前和现在作为两个端点会产生不连续性，因为每一个新的一天都会有效地重新开始曲线。

最简单的方法是固定开始和结束的日期，以便在多次运行中都有一个总体的曲线。换句话说，选择一个未来的结束日期并坚持下去（比如未来5年）。在这里，只需在选择查询中对时间戳进行过滤，就可以生成你需要的日期范围。

select
    date_trunc('day', ts)::date AS day,
    count(1) as total_timestamps
from quickly_increasing_timestamps
where ts > '2021-02-02'::timestamp and ts < 2021-02-03'::timestamp
group by day
order by day asc

总结

这就是了。一套漂亮的自动生成的时间戳，遵循我们想要的任何曲线。在未来的文章中，我们将深入探讨如何使用这些合成时间戳来建立一整套真实的客户行为。