欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Generate Series in Redshift and MySQL_MySQL

程序员文章站 2022-06-14 15:06:56
...
A lot of the charts and tables made inPeriscopeare time series, and the queries behind them are often easier when you can join and aggregate against a list of dates. Not having a complete list of dates causes gaps in the results, changing them in a misleading way:

Generate Series in Redshift and MySQL_MySQL

Postgres has a great function for generating a list of dates (seeUse generate_series to get continuous results), and making a list of the last 60 days withgenerate_seriesis easy:

select now()::date - generate_series(0, 59)

Accomplishing the same thing in Redshift and MySQL requires a little more work.

Date Series from a Numbers Table

The simplest alternative togenerate_seriesis to create a table containing a continuous list of numbers, starting at 0, and select from that table. (If you have a table with a sequentialidcolumn and never delete rows from it, you can just select theidcolumn from that table instead of creating a new numbers table).

select n from numbers;

Returns this list of rows: 0, 1, 2, 3...

Now that you have a numbers table, convert each number into a date:

Redshift:

select (getdate()::date - n)::date from numbers

MySQL:

select date_sub(date(now()), interval n day) from numbers

A numbers table is more convenient than a dates table since it never needs to be refreshed with new dates.

Redshift: Date Series using Window Functions

If you don't have the option to create a numbers table, you can build one on the fly using a window function. All you need is a table that has at least as many rows as the number of dates desired. Using a window function, number the rows in any table to get a list of numbers, and then convert that to a list of dates:

select row_number() over (order by true) as nfrom users limit 60

And now creating the list of dates directly:

select (getdate()::date - row_number() over (order by true))::date as nfrom users limit 60

MySQL: Date Series using Variables

With variables in MySQL, we can generate a numbers table by treating a select statement as a for loop:

set @n:=-1;select (select @n:= @n+1) nfrom users limit 60

And now creating the list of dates directly:

set @n:=date(now() + interval 1 day);select (select @n:= @n - interval 1 day) nfrom users limit 60

Now that we've made a list of dates, aggregating and joining data from other tables for time series charts is a breeze!