sequence

sequence

description

alias of array_range function

Syntax

  1. ARRAY<Int> sequence(Int end)
  2. ARRAY<Int> sequence(Int start, Int end)
  3. ARRAY<Int> sequence(Int start, Int end, Int step)
  4. ARRAY<Datetime> sequence(Datetime start_datetime, Datetime end_datetime)
  5. ARRAY<Datetime> sequence(Datetime start_datetime, Datetime end_datetime, INTERVAL Int interval_step UNIT)

  1. To generate array of int: The parameters are all positive integers. start default value is 0, and step default value is 1. Return the array which numbers from start to end - 1 by step.

  2. To generate array of datetime: At least taking two parameters. The first two parameters are all datetimev2, the third is positive integer. If the third part is missing, INTERVAL 1 DAY will be default value. UNIT supports YEAR/MONTH/WEEK/DAY/HOUR/MINUTE/SECOND. Return the array of datetimev2 between start_datetime and closest to end_datetime by interval_step UNIT.

notice

if the 3rd parameter step/interval_step is negative or zero, the function will return NULL

example

  1. mysql> select sequence(10);
  2. +--------------------------------+
  3. | sequence(10)                   |
  4. +--------------------------------+
  5. | [0, 1, 2, 3, 4, 5, 6, 7, 8, 9] |
  6. +--------------------------------+
  8. mysql> select sequence(10,20);
  9. +------------------------------------------+
  10. | sequence(10, 20)                         |
  11. +------------------------------------------+
  12. | [10, 11, 12, 13, 14, 15, 16, 17, 18, 19] |
  13. +------------------------------------------+
  15. mysql> select sequence(0,20,2);
  16. +-------------------------------------+
  17. | sequence(0, 20, 2)                  |
  18. +-------------------------------------+
  19. | [0, 2, 4, 6, 8, 10, 12, 14, 16, 18] |
  20. +-------------------------------------+
  22. mysql> select sequence(cast('2022-05-15 12:00:00' as datetimev2(0)), cast('2022-05-17 12:00:00' as datetimev2(0))) AS sequence_default;
  23. +------------------------------------------------+
  24. | sequence_default                               |
  25. +------------------------------------------------+
  26. | ["2022-05-15 12:00:00", "2022-05-16 12:00:00"] |
  27. +------------------------------------------------+
  29. mysql> select sequence(cast('2019-05-15 12:00:00' as datetimev2(0)), cast('2022-05-17 12:00:00' as datetimev2(0)), interval 2 year) as sequence_2_year;
  30. +------------------------------------------------+
  31. | sequence_2_year                                |
  32. +------------------------------------------------+
  33. | ["2019-05-15 12:00:00", "2021-05-15 12:00:00"] |
  34. +------------------------------------------------+

keywords

ARRAY, RANGE, SEQUENCE