| # Licensed to the Apache Software Foundation (ASF) under one |
| # or more contributor license agreements. See the NOTICE file |
| # distributed with this work for additional information |
| # regarding copyright ownership. The ASF licenses this file |
| # to you under the Apache License, Version 2.0 (the |
| # "License"); you may not use this file except in compliance |
| # with the License. You may obtain a copy of the License at |
| # |
| # http://www.apache.org/licenses/LICENSE-2.0 |
| # |
| # Unless required by applicable law or agreed to in writing, |
| # software distributed under the License is distributed on an |
| # "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| # KIND, either express or implied. See the License for the |
| # specific language governing permissions and limitations |
| # under the License. |
| |
| # Use generate_series |
| SELECT * FROM generate_series(1, 10); |
| -- |
| +---------------+ |
| |generate_series| |
| +---------------+ |
| | 1| |
| | 2| |
| | 3| |
| | 4| |
| | 5| |
| | 6| |
| | 7| |
| | 8| |
| | 9| |
| | 10| |
| +---------------+ |
| == |
| |
| # Use generate_series: non-default step width |
| SELECT * FROM generate_series(1, 10, 2); |
| -- |
| +---------------+ |
| |generate_series| |
| +---------------+ |
| | 1| |
| | 3| |
| | 5| |
| | 7| |
| | 9| |
| +---------------+ |
| == |
| |
| # Use generate_series: negative step width |
| SELECT * FROM generate_series(10, 1, -2); |
| -- |
| +---------------+ |
| |generate_series| |
| +---------------+ |
| | 10| |
| | 8| |
| | 6| |
| | 4| |
| | 2| |
| +---------------+ |
| == |
| |
| # Use generate_series: float number |
| SELECT * FROM generate_series(1.5, 5.5); |
| -- |
| +------------------------+ |
| |generate_series | |
| +------------------------+ |
| | 1.5| |
| | 2.5| |
| | 3.5| |
| | 4.5| |
| | 5.5| |
| +------------------------+ |
| == |
| |
| # Use generate_series: float number step width |
| SELECT * FROM generate_series(1, 3, 0.5); |
| -- |
| +------------------------+ |
| |generate_series | |
| +------------------------+ |
| | 1| |
| | 1.5| |
| | 2| |
| | 2.5| |
| | 3| |
| +------------------------+ |
| == |
| |
| # Use generate_series: extra columns |
| SELECT i, i+1, i*2 FROM generate_series(1, 3, 0.5) AS gs(i); |
| -- |
| +------------------------+------------------------+------------------------+ |
| |i |(i+1) |(i*2) | |
| +------------------------+------------------------+------------------------+ |
| | 1| 2| 2| |
| | 1.5| 2.5| 3| |
| | 2| 3| 4| |
| | 2.5| 3.5| 5| |
| | 3| 4| 6| |
| +------------------------+------------------------+------------------------+ |
| == |
| |
| |
| # Use generate_series: cross product |
| SELECT i, j, i+j FROM generate_series(1, 5) AS gs1(i), generate_series(0, 1, 0.5) AS gs2(j); |
| -- |
| +-----------+------------------------+------------------------+ |
| |i |j |(i+j) | |
| +-----------+------------------------+------------------------+ |
| | 1| 0| 1| |
| | 1| 0.5| 1.5| |
| | 1| 1| 2| |
| | 2| 0| 2| |
| | 2| 0.5| 2.5| |
| | 2| 1| 3| |
| | 3| 0| 3| |
| | 3| 0.5| 3.5| |
| | 3| 1| 4| |
| | 4| 0| 4| |
| | 4| 0.5| 4.5| |
| | 4| 1| 5| |
| | 5| 0| 5| |
| | 5| 0.5| 5.5| |
| | 5| 1| 6| |
| +-----------+------------------------+------------------------+ |
| == |
| |
| # Use generate_series: join alias |
| SELECT * |
| FROM (SELECT i FROM generate_series(1, 10) as GS(i)) AS R(r1), |
| (SELECT i FROM generate_series(1, 10) as GS(i)) AS S(s1) |
| WHERE R.r1 = S.s1; |
| -- |
| +-----------+-----------+ |
| |r1 |s1 | |
| +-----------+-----------+ |
| | 1| 1| |
| | 2| 2| |
| | 3| 3| |
| | 4| 4| |
| | 5| 5| |
| | 6| 6| |
| | 7| 7| |
| | 8| 8| |
| | 9| 9| |
| | 10| 10| |
| +-----------+-----------+ |
| == |
| |
| # Use generate_series: filter |
| SELECT i, j, k |
| FROM generate_series(1, 9) AS gs1(i), |
| generate_series(1, 9) AS gs2(j), |
| generate_series(1, 81) AS gs3(k) |
| WHERE i <= j AND i * j = k; |
| -- |
| +-----------+-----------+-----------+ |
| |i |j |k | |
| +-----------+-----------+-----------+ |
| | 1| 1| 1| |
| | 1| 2| 2| |
| | 1| 3| 3| |
| | 1| 4| 4| |
| | 1| 5| 5| |
| | 1| 6| 6| |
| | 1| 7| 7| |
| | 1| 8| 8| |
| | 1| 9| 9| |
| | 2| 2| 4| |
| | 2| 3| 6| |
| | 2| 4| 8| |
| | 2| 5| 10| |
| | 2| 6| 12| |
| | 2| 7| 14| |
| | 2| 8| 16| |
| | 2| 9| 18| |
| | 3| 3| 9| |
| | 3| 4| 12| |
| | 3| 5| 15| |
| | 3| 6| 18| |
| | 3| 7| 21| |
| | 3| 8| 24| |
| | 3| 9| 27| |
| | 4| 4| 16| |
| | 4| 5| 20| |
| | 4| 6| 24| |
| | 4| 7| 28| |
| | 4| 8| 32| |
| | 4| 9| 36| |
| | 5| 5| 25| |
| | 5| 6| 30| |
| | 5| 7| 35| |
| | 5| 8| 40| |
| | 5| 9| 45| |
| | 6| 6| 36| |
| | 6| 7| 42| |
| | 6| 8| 48| |
| | 6| 9| 54| |
| | 7| 7| 49| |
| | 7| 8| 56| |
| | 7| 9| 63| |
| | 8| 8| 64| |
| | 8| 9| 72| |
| | 9| 9| 81| |
| +-----------+-----------+-----------+ |
| == |
| |
| # Use generate_series: aggregation |
| SELECT count(*) FROM generate_series(1, 1000); |
| -- |
| +--------------------+ |
| |count(*) | |
| +--------------------+ |
| | 1000| |
| +--------------------+ |
| == |
| |
| SELECT sum(i) FROM generate_series(1, 1000) AS gs(i); |
| -- |
| +--------------------+ |
| |sum(i) | |
| +--------------------+ |
| | 500500| |
| +--------------------+ |
| == |
| |
| SELECT avg(i) FROM generate_series(1, 1000) AS gs(i); |
| -- |
| +------------------------+ |
| |avg(i) | |
| +------------------------+ |
| | 500.5| |
| +------------------------+ |
| == |
| |
| # Use generate_series: error messages |
| SELECT * FROM generate_series(); |
| -- |
| ERROR: Invalid number of arguments (1 : 15) |
| SELECT * FROM generate_series(); |
| ^ |
| == |
| |
| SELECT * FROM generate_series('a', 3); |
| -- |
| ERROR: Invalid argument types (1 : 15) |
| SELECT * FROM generate_series('a', 3); |
| ^ |
| == |
| |
| SELECT * FROM generate_series(1, 5, -1); |
| -- |
| ERROR: Invalid step width (1 : 15) |
| SELECT * FROM generate_series(1, 5, -1); |
| ^ |
| == |
| |
