| # 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. |
| |
| SELECT * |
| FROM test |
| WHERE int_col < 5 |
| ORDER BY int_col DESC, |
| long_col ASC |
| LIMIT 5; |
| -- |
| +-----------+--------------------+---------------+------------------------+--------------------+ |
| |int_col |long_col |float_col |double_col |char_col | |
| +-----------+--------------------+---------------+------------------------+--------------------+ |
| | 4| 16| 2| 8| 4 2.000000| |
| | 2| 4| 1.41421354| 2.8284271247461903| 2 1.414214| |
| | -1| 1| 1| -1| -1 1.000000| |
| | -3| 9| 1.73205078| -5.196152422706632| -3 1.732051| |
| | -5| 25| 2.23606801| -11.180339887498949| -5 2.236068| |
| +-----------+--------------------+---------------+------------------------+--------------------+ |
| == |
| |
| # Simple SELECT. |
| SELECT * |
| FROM test |
| WHERE int_col < 10 |
| AND long_col < 100 |
| AND double_col < 0; |
| -- |
| +-----------+--------------------+---------------+------------------------+--------------------+ |
| |int_col |long_col |float_col |double_col |char_col | |
| +-----------+--------------------+---------------+------------------------+--------------------+ |
| | -1| 1| 1| -1| -1 1.000000| |
| | -3| 9| 1.73205078| -5.196152422706632| -3 1.732051| |
| | -5| 25| 2.23606801| -11.180339887498949| -5 2.236068| |
| | -7| 49| 2.64575124| -18.520259177452136| -7 2.645751| |
| | -9| 81| 3| -27| -9 3.000000| |
| +-----------+--------------------+---------------+------------------------+--------------------+ |
| == |
| |
| # Modulo operator |
| SELECT int_col % 2, |
| long_col % (int_col * int_col), |
| float_col % 1, |
| double_col % 1.0 |
| FROM test; |
| -- |
| +-----------+----------------------------+---------------+------------------------+ |
| |(int_col%2)|(long_col%(int_col*int_col))|(float_col%1) |(double_col%1.0) | |
| +-----------+----------------------------+---------------+------------------------+ |
| | NULL| NULL| 0| NULL| |
| | -1| 0| 0| 0| |
| | 0| 0| 0.414213538| 0.82842712474619029| |
| | -1| 0| 0.732050776| -0.19615242270663202| |
| | 0| 0| 0| 0| |
| | -1| 0| 0.23606801| -0.18033988749894903| |
| | 0| 0| 0.449489832| 0.69693845669906729| |
| | -1| 0| 0.645751238| -0.5202591774521359| |
| | 0| 0| 0.828427076| 0.62741699796952233| |
| | -1| 0| 0| 0| |
| | NULL| NULL| 0.162277699| NULL| |
| | -1| 0| 0.31662488| -0.48287269390939969| |
| | 0| 0| 0.464101553| 0.56921938165305619| |
| | -1| 0| 0.605551243| -0.87216658103185551| |
| | 0| 0| 0.741657495| 0.3832034148351795| |
| | -1| 0| 0.872983456| -0.094750193111252656| |
| | 0| 0| 0| 0| |
| | -1| 0| 0.123105526| -0.092795635500223739| |
| | 0| 0| 0.242640495| 0.36753236814712409| |
| | -1| 0| 0.358899117| -0.81907992727280998| |
| | NULL| NULL| 0.472136021| NULL| |
| | -1| 0| 0.582575798| -0.23408959407264263| |
| | 0| 0| 0.690415859| 0.18914671611545941| |
| | -1| 0| 0.79583168| -0.30412503619254494| |
| | 0| 0| 0.898979664| 0.5755076535925383| |
| +-----------+----------------------------+---------------+------------------------+ |
| == |
| |
| SELECT i, |
| i/4 AS bit2, |
| (i%4)/2 AS bit1, |
| i%2 AS bit0 |
| FROM generate_series(0, 7) AS gs(i); |
| |
| -- |
| +-----------+-----------+-----------+-----------+ |
| |i |bit2 |bit1 |bit0 | |
| +-----------+-----------+-----------+-----------+ |
| | 0| 0| 0| 0| |
| | 1| 0| 0| 1| |
| | 2| 0| 1| 0| |
| | 3| 0| 1| 1| |
| | 4| 1| 0| 0| |
| | 5| 1| 0| 1| |
| | 6| 1| 1| 0| |
| | 7| 1| 1| 1| |
| +-----------+-----------+-----------+-----------+ |
| == |
| |
| SELECT * |
| FROM test |
| WHERE int_col < 10 |
| AND long_col < 100; |
| -- |
| +-----------+--------------------+---------------+------------------------+--------------------+ |
| |int_col |long_col |float_col |double_col |char_col | |
| +-----------+--------------------+---------------+------------------------+--------------------+ |
| | -1| 1| 1| -1| -1 1.000000| |
| | 2| 4| 1.41421354| 2.8284271247461903| 2 1.414214| |
| | -3| 9| 1.73205078| -5.196152422706632| -3 1.732051| |
| | 4| 16| 2| 8| 4 2.000000| |
| | -5| 25| 2.23606801| -11.180339887498949| -5 2.236068| |
| | 6| 36| 2.44948983| 14.696938456699067| 6 2.449490| |
| | -7| 49| 2.64575124| -18.520259177452136| -7 2.645751| |
| | 8| 64| 2.82842708| 22.627416997969522| 8 2.828427| |
| | -9| 81| 3| -27| -9 3.000000| |
| +-----------+--------------------+---------------+------------------------+--------------------+ |
| == |
| |
| SELECT int_col, |
| int_col+100 AS alias_col, |
| char_col AS char_col |
| FROM test |
| WHERE int_col < 10 |
| AND long_col < 100; |
| -- |
| +-----------+-----------+--------------------+ |
| |int_col |alias_col |char_col | |
| +-----------+-----------+--------------------+ |
| | -1| 99| -1 1.000000| |
| | 2| 102| 2 1.414214| |
| | -3| 97| -3 1.732051| |
| | 4| 104| 4 2.000000| |
| | -5| 95| -5 2.236068| |
| | 6| 106| 6 2.449490| |
| | -7| 93| -7 2.645751| |
| | 8| 108| 8 2.828427| |
| | -9| 91| -9 3.000000| |
| +-----------+-----------+--------------------+ |
| == |
| |
| # Nested queries. |
| SELECT * |
| FROM |
| (SELECT * |
| FROM |
| (SELECT * |
| FROM |
| (SELECT * |
| FROM test |
| WHERE long_col < 100) a |
| WHERE double_col<0) a |
| WHERE long_col < 200) a |
| WHERE int_col < 10; |
| -- |
| +-----------+--------------------+---------------+------------------------+--------------------+ |
| |int_col |long_col |float_col |double_col |char_col | |
| +-----------+--------------------+---------------+------------------------+--------------------+ |
| | -1| 1| 1| -1| -1 1.000000| |
| | -3| 9| 1.73205078| -5.196152422706632| -3 1.732051| |
| | -5| 25| 2.23606801| -11.180339887498949| -5 2.236068| |
| | -7| 49| 2.64575124| -18.520259177452136| -7 2.645751| |
| | -9| 81| 3| -27| -9 3.000000| |
| +-----------+--------------------+---------------+------------------------+--------------------+ |
| == |
| |
| WITH subquery1 AS |
| (SELECT * |
| FROM test |
| WHERE int_col < 10), |
| subquery2 AS |
| (SELECT * |
| FROM subquery1 |
| WHERE long_col < 100) |
| SELECT * |
| FROM subquery2 |
| WHERE double_col < 0; |
| -- |
| [same as above] |
| == |
| |
| SELECT NULL, |
| 1/NULL, |
| int_col+NULL, |
| int_col+long_col, |
| float_col+NULL, |
| -NULL |
| FROM test |
| WHERE long_col < 100 |
| ORDER BY 4 NULLS LAST; |
| -- |
| +----+--------+--------------+--------------------+----------------+-----+ |
| |NULL|(1/NULL)|(int_col+NULL)|(int_col+long_col) |(float_col+NULL)|-NULL| |
| +----+--------+--------------+--------------------+----------------+-----+ |
| |NULL| NULL| NULL| 0| NULL| NULL| |
| |NULL| NULL| NULL| 6| NULL| NULL| |
| |NULL| NULL| NULL| 6| NULL| NULL| |
| |NULL| NULL| NULL| 20| NULL| NULL| |
| |NULL| NULL| NULL| 20| NULL| NULL| |
| |NULL| NULL| NULL| 42| NULL| NULL| |
| |NULL| NULL| NULL| 42| NULL| NULL| |
| |NULL| NULL| NULL| 72| NULL| NULL| |
| |NULL| NULL| NULL| 72| NULL| NULL| |
| |NULL| NULL| NULL| NULL| NULL| NULL| |
| +----+--------+--------------+--------------------+----------------+-----+ |
| == |
| |
| SELECT int_col " |
| this |
| |
| is multi-line |
| |
| column name |
| " |
| FROM test |
| WHERE int_col < 10 |
| AND long_col < 100 |
| AND double_col < 0; |
| -- |
| +----------------------------------+ |
| | |
| this |
| |
| is multi-line |
| |
| column name |
| | |
| +----------------------------------+ |
| | -1| |
| | -3| |
| | -5| |
| | -7| |
| | -9| |
| +----------------------------------+ |
| == |
| |
| # Alias support. |
| SELECT int_col+100 AS alias_col, |
| char_col AS char_col |
| FROM test |
| WHERE int_col < 10 |
| AND long_col < 100; |
| -- |
| +-----------+--------------------+ |
| |alias_col |char_col | |
| +-----------+--------------------+ |
| | 99| -1 1.000000| |
| | 102| 2 1.414214| |
| | 97| -3 1.732051| |
| | 104| 4 2.000000| |
| | 95| -5 2.236068| |
| | 106| 6 2.449490| |
| | 93| -7 2.645751| |
| | 108| 8 2.828427| |
| | 91| -9 3.000000| |
| +-----------+--------------------+ |
| == |
| |
| SELECT a.int_col, |
| a.int_col*b.int_col, |
| b.long_col |
| FROM test AS a, |
| test AS b |
| WHERE a.int_col*b.int_col = b.long_col; |
| -- |
| +-----------+---------------------+--------------------+ |
| |int_col |(a.int_col*b.int_col)|long_col | |
| +-----------+---------------------+--------------------+ |
| | -1| 1| 1| |
| | 2| 4| 4| |
| | -3| 9| 9| |
| | 4| 16| 16| |
| | -5| 25| 25| |
| | 6| 36| 36| |
| | -7| 49| 49| |
| | 8| 64| 64| |
| | -9| 81| 81| |
| | -11| 121| 121| |
| | 12| 144| 144| |
| | -13| 169| 169| |
| | 14| 196| 196| |
| | -15| 225| 225| |
| | 16| 256| 256| |
| | -17| 289| 289| |
| | 18| 324| 324| |
| | -19| 361| 361| |
| | -21| 441| 441| |
| | 22| 484| 484| |
| | -23| 529| 529| |
| | 24| 576| 576| |
| +-----------+---------------------+--------------------+ |
| == |
| |
| # The nested loops join is not a self-join, because the predicate "a.int_col<10" is pushed under the join, which results |
| # in a new relation. |
| SELECT a.int_col, |
| a.int_col*b.int_col, |
| b.long_col |
| FROM test AS a, |
| test AS b |
| WHERE a.long_col < 100 |
| AND a.int_col*b.int_col = b.long_col; |
| -- |
| +-----------+---------------------+--------------------+ |
| |int_col |(a.int_col*b.int_col)|long_col | |
| +-----------+---------------------+--------------------+ |
| | -1| 1| 1| |
| | 2| 4| 4| |
| | -3| 9| 9| |
| | 4| 16| 16| |
| | -5| 25| 25| |
| | 6| 36| 36| |
| | -7| 49| 49| |
| | 8| 64| 64| |
| | -9| 81| 81| |
| +-----------+---------------------+--------------------+ |
| == |
| |
| SELECT a.int_col, |
| b.int_col |
| FROM test AS a, |
| test AS b |
| WHERE a.int_col = b.int_col; |
| -- |
| +-----------+-----------+ |
| |int_col |int_col | |
| +-----------+-----------+ |
| | -1| -1| |
| | 2| 2| |
| | -3| -3| |
| | 4| 4| |
| | -5| -5| |
| | 6| 6| |
| | -7| -7| |
| | 8| 8| |
| | -9| -9| |
| | -11| -11| |
| | 12| 12| |
| | -13| -13| |
| | 14| 14| |
| | -15| -15| |
| | 16| 16| |
| | -17| -17| |
| | 18| 18| |
| | -19| -19| |
| | -21| -21| |
| | 22| 22| |
| | -23| -23| |
| | 24| 24| |
| +-----------+-----------+ |
| == |
| |
| # This is not a self-join, because there is a Select under the HashJoin for "a.long_col < 50". |
| SELECT a.int_col, |
| b.int_col |
| FROM test AS a, |
| test AS b |
| WHERE a.long_col < 50 |
| AND a.int_col = b.int_col; |
| -- |
| +-----------+-----------+ |
| |int_col |int_col | |
| +-----------+-----------+ |
| | -1| -1| |
| | 2| 2| |
| | -3| -3| |
| | 4| 4| |
| | -5| -5| |
| | 6| 6| |
| | -7| -7| |
| +-----------+-----------+ |
| == |
| |
| # Hash join does not support a join predicate with two different types of attributes. |
| SELECT a.int_col, |
| b.long_col |
| FROM test AS a, |
| test AS b |
| WHERE a.int_col = b.long_col; |
| -- |
| ERROR: Equality join predicate between two attributes of different types is not allowed in HashJoin |
| == |
| |
| # Join with char-type attributes. |
| SELECT a.char_col, |
| b.char_col, |
| a.int_col |
| FROM test AS a, |
| test AS b |
| WHERE a.char_col = b.char_col |
| AND a.long_col < 100; |
| -- |
| +--------------------+--------------------+-----------+ |
| |char_col |char_col |int_col | |
| +--------------------+--------------------+-----------+ |
| | 0 0.000000| 0 0.000000| NULL| |
| | -1 1.000000| -1 1.000000| -1| |
| | 2 1.414214| 2 1.414214| 2| |
| | -3 1.732051| -3 1.732051| -3| |
| | 4 2.000000| 4 2.000000| 4| |
| | -5 2.236068| -5 2.236068| -5| |
| | 6 2.449490| 6 2.449490| 6| |
| | -7 2.645751| -7 2.645751| -7| |
| | 8 2.828427| 8 2.828427| 8| |
| | -9 3.000000| -9 3.000000| -9| |
| +--------------------+--------------------+-----------+ |
| == |
| |
| # Cross-product |
| SELECT a.int_col, |
| b.int_col |
| FROM test AS a, |
| test AS b |
| WHERE a.int_col BETWEEN 0 AND 3 |
| AND b.int_col BETWEEN 0 AND 3; |
| -- |
| +-----------+-----------+ |
| |int_col |int_col | |
| +-----------+-----------+ |
| | 2| 2| |
| +-----------+-----------+ |
| == |
| |
| # Mixed multiple nested loops joins and hash joins |
| SELECT a.int_col, |
| b.int_col, |
| c.float_col, |
| d.float_col+a.int_col, |
| d.float_col, |
| b.double_col, |
| c.int_col |
| FROM test AS a, |
| test AS b, |
| test AS c, |
| (SELECT a.float_col |
| FROM test AS a, |
| test AS b |
| WHERE a.int_col < 5) AS d |
| WHERE a.int_col = b.int_col |
| AND a.long_col = b.long_col |
| AND c.float_col > d.float_col+a.int_col |
| AND d.float_col <= 1 |
| AND a.int_col BETWEEN 0 AND 5 |
| AND c.float_col > 4.7; |
| -- |
| +-----------+-----------+---------------+-----------------------+---------------+------------------------+-----------+ |
| |int_col |int_col |float_col |(d.float_col+a.int_col)|float_col |double_col |int_col | |
| +-----------+-----------+---------------+-----------------------+---------------+------------------------+-----------+ |
| | 2| 2| 4.79583168| 3| 1| 2.8284271247461903| -23| |
| | 2| 2| 4.79583168| 3| 1| 2.8284271247461903| -23| |
| | 2| 2| 4.79583168| 3| 1| 2.8284271247461903| -23| |
| | 2| 2| 4.79583168| 3| 1| 2.8284271247461903| -23| |
| | 2| 2| 4.79583168| 3| 1| 2.8284271247461903| -23| |
| | 2| 2| 4.79583168| 3| 1| 2.8284271247461903| -23| |
| | 2| 2| 4.79583168| 3| 1| 2.8284271247461903| -23| |
| | 2| 2| 4.79583168| 3| 1| 2.8284271247461903| -23| |
| | 2| 2| 4.79583168| 3| 1| 2.8284271247461903| -23| |
| | 2| 2| 4.79583168| 3| 1| 2.8284271247461903| -23| |
| | 2| 2| 4.79583168| 3| 1| 2.8284271247461903| -23| |
| | 2| 2| 4.79583168| 3| 1| 2.8284271247461903| -23| |
| | 2| 2| 4.79583168| 3| 1| 2.8284271247461903| -23| |
| | 2| 2| 4.79583168| 3| 1| 2.8284271247461903| -23| |
| | 2| 2| 4.79583168| 3| 1| 2.8284271247461903| -23| |
| | 2| 2| 4.79583168| 3| 1| 2.8284271247461903| -23| |
| | 2| 2| 4.79583168| 3| 1| 2.8284271247461903| -23| |
| | 2| 2| 4.79583168| 3| 1| 2.8284271247461903| -23| |
| | 2| 2| 4.79583168| 3| 1| 2.8284271247461903| -23| |
| | 2| 2| 4.79583168| 3| 1| 2.8284271247461903| -23| |
| | 2| 2| 4.79583168| 3| 1| 2.8284271247461903| -23| |
| | 2| 2| 4.79583168| 3| 1| 2.8284271247461903| -23| |
| | 2| 2| 4.79583168| 3| 1| 2.8284271247461903| -23| |
| | 2| 2| 4.79583168| 3| 1| 2.8284271247461903| -23| |
| | 2| 2| 4.79583168| 3| 1| 2.8284271247461903| -23| |
| | 2| 2| 4.89897966| 3| 1| 2.8284271247461903| 24| |
| | 2| 2| 4.89897966| 3| 1| 2.8284271247461903| 24| |
| | 2| 2| 4.89897966| 3| 1| 2.8284271247461903| 24| |
| | 2| 2| 4.89897966| 3| 1| 2.8284271247461903| 24| |
| | 2| 2| 4.89897966| 3| 1| 2.8284271247461903| 24| |
| | 2| 2| 4.89897966| 3| 1| 2.8284271247461903| 24| |
| | 2| 2| 4.89897966| 3| 1| 2.8284271247461903| 24| |
| | 2| 2| 4.89897966| 3| 1| 2.8284271247461903| 24| |
| | 2| 2| 4.89897966| 3| 1| 2.8284271247461903| 24| |
| | 2| 2| 4.89897966| 3| 1| 2.8284271247461903| 24| |
| | 2| 2| 4.89897966| 3| 1| 2.8284271247461903| 24| |
| | 2| 2| 4.89897966| 3| 1| 2.8284271247461903| 24| |
| | 2| 2| 4.89897966| 3| 1| 2.8284271247461903| 24| |
| | 2| 2| 4.89897966| 3| 1| 2.8284271247461903| 24| |
| | 2| 2| 4.89897966| 3| 1| 2.8284271247461903| 24| |
| | 2| 2| 4.89897966| 3| 1| 2.8284271247461903| 24| |
| | 2| 2| 4.89897966| 3| 1| 2.8284271247461903| 24| |
| | 2| 2| 4.89897966| 3| 1| 2.8284271247461903| 24| |
| | 2| 2| 4.89897966| 3| 1| 2.8284271247461903| 24| |
| | 2| 2| 4.89897966| 3| 1| 2.8284271247461903| 24| |
| | 2| 2| 4.89897966| 3| 1| 2.8284271247461903| 24| |
| | 2| 2| 4.89897966| 3| 1| 2.8284271247461903| 24| |
| | 2| 2| 4.89897966| 3| 1| 2.8284271247461903| 24| |
| | 2| 2| 4.89897966| 3| 1| 2.8284271247461903| 24| |
| | 2| 2| 4.89897966| 3| 1| 2.8284271247461903| 24| |
| +-----------+-----------+---------------+-----------------------+---------------+------------------------+-----------+ |
| == |
| |
| SELECT COUNT(*) |
| FROM |
| (SELECT a.float_col |
| FROM test a, |
| test b |
| WHERE a.float_col > 1) c; |
| -- |
| +--------------------+ |
| |COUNT(*) | |
| +--------------------+ |
| | 575| |
| +--------------------+ |
| == |
| |
| SELECT DATE '2000-02-29' + INTERVAL '1 year', |
| DATE '1998-03-31' + INTERVAL '1 month', |
| DATE '2000-03-31' - INTERVAL '1 month', |
| DATE '2000-01-31' + INTERVAL '1 month', |
| DATETIME '2000-02-29' + INTERVAL '1 day', |
| DATE '2000-02-29' - INTERVAL '1 year' |
| FROM test |
| WHERE int_col = 2; |
| -- |
| +------------------------------------------------+-------------------------------------------------+-------------------------------------------------+-------------------------------------------------+--------------------------------------------------+------------------------------------------------+ |
| |(Date('2000-02-29')+YearMonthInterval('1 year'))|(Date('1998-03-31')+YearMonthInterval('1 month'))|(Date('2000-03-31')-YearMonthInterval('1 month'))|(Date('2000-01-31')+YearMonthInterval('1 month'))|(Datetime('2000-02-29')+DatetimeInterval('1 day'))|(Date('2000-02-29')-YearMonthInterval('1 year'))| |
| +------------------------------------------------+-------------------------------------------------+-------------------------------------------------+-------------------------------------------------+--------------------------------------------------+------------------------------------------------+ |
| | 2001-02-28| 1998-04-30| 2000-02-29| 2000-02-29| 2000-03-01T00:00:00| 1999-02-28| |
| +------------------------------------------------+-------------------------------------------------+-------------------------------------------------+-------------------------------------------------+--------------------------------------------------+------------------------------------------------+ |
| == |
| |
| SELECT DATETIME '2000-02-29' - DATETIME '2000-02-28', |
| DATETIME '2000-02-29' - DATETIME '1999-02-28' |
| FROM test |
| WHERE int_col = 2; |
| -- |
| +-----------------------------------------------+-----------------------------------------------+ |
| |(Datetime('2000-02-29')-Datetime('2000-02-28'))|(Datetime('2000-02-29')-Datetime('1999-02-28'))| |
| +-----------------------------------------------+-----------------------------------------------+ |
| | 1 day 00:00:00| 366 days 00:00:00| |
| +-----------------------------------------------+-----------------------------------------------+ |
| == |
| |
| SELECT INTERVAL '1 year' + INTERVAL '1 month', |
| INTERVAL '1 year' + INTERVAL '13 month', |
| INTERVAL '21 day' + INTERVAL '14 day', |
| INTERVAL '21 day' * 4 |
| FROM test |
| WHERE int_col = 2; |
| -- |
| +----------------------------------------------------------+-----------------------------------------------------------+-------------------------------------------------------+----------------------------------------+ |
| |(YearMonthInterval('1 year')+YearMonthInterval('1 month'))|(YearMonthInterval('1 year')+YearMonthInterval('13 month'))|(DatetimeInterval('21 day')+DatetimeInterval('14 day'))|(DatetimeInterval('21 day')*4) | |
| +----------------------------------------------------------+-----------------------------------------------------------+-------------------------------------------------------+----------------------------------------+ |
| | 1 year 1 mon| 2 years 1 mon| 35 days 00:00:00| 84 days 00:00:00| |
| +----------------------------------------------------------+-----------------------------------------------------------+-------------------------------------------------------+----------------------------------------+ |
| == |
| |
| SELECT DATE '+1980-02-29' + INTERVAL '1 year' |
| FROM test |
| WHERE int_col = 2; |
| -- |
| +-------------------------------------------------+ |
| |(Date('+1980-02-29')+YearMonthInterval('1 year'))| |
| +-------------------------------------------------+ |
| | 1981-02-28| |
| +-------------------------------------------------+ |
| == |
| |
| SELECT int_col |
| FROM test |
| GROUP BY int_col |
| ORDER BY int_col; |
| -- |
| +-----------+ |
| |int_col | |
| +-----------+ |
| | -23| |
| | -21| |
| | -19| |
| | -17| |
| | -15| |
| | -13| |
| | -11| |
| | -9| |
| | -7| |
| | -5| |
| | -3| |
| | -1| |
| | 2| |
| | 4| |
| | 6| |
| | 8| |
| | 12| |
| | 14| |
| | 16| |
| | 18| |
| | 22| |
| | 24| |
| +-----------+ |
| == |
| |
| SELECT COUNT(*), |
| COUNT(1), |
| COUNT(0), |
| SUM(int_col) / COUNT(*), |
| AVG(int_col+0) * COUNT(1), |
| MAX(double_col+100), |
| MIN(float_col+1) |
| FROM test; |
| -- |
| +--------------------+--------------------+--------------------+-----------------------+---------------------------+------------------------+------------------+ |
| |COUNT(*) |COUNT(1) |COUNT(0) |(SUM(int_col)/COUNT(*))|(AVG((int_col+0))*COUNT(1))|MAX((double_col+100)) |MIN((float_col+1))| |
| +--------------------+--------------------+--------------------+-----------------------+---------------------------+------------------------+------------------+ |
| | 25| 25| 25| 0| -20.454545454545457| 217.57550765359252| 1| |
| +--------------------+--------------------+--------------------+-----------------------+---------------------------+------------------------+------------------+ |
| == |
| |
| SELECT COUNT(NULL) |
| FROM test; |
| -- |
| +--------------------+ |
| |COUNT(NULL) | |
| +--------------------+ |
| | 0| |
| +--------------------+ |
| == |
| |
| SELECT long_col/100 AS group_col, |
| COUNT(*), |
| SUM(int_col) |
| FROM test |
| GROUP BY group_col |
| HAVING MIN(float_col) > 0; |
| -- |
| +--------------------+--------------------+--------------------+ |
| |group_col |COUNT(*) |SUM(int_col) | |
| +--------------------+--------------------+--------------------+ |
| | 1| 5| 2| |
| | 2| 3| -16| |
| | 3| 2| -1| |
| | 4| 3| 1| |
| | 5| 2| 1| |
| +--------------------+--------------------+--------------------+ |
| == |
| |
| SELECT COUNT(*), |
| long_col/100 AS group_col1, |
| long_col/50 AS group_col2 |
| FROM test |
| GROUP BY group_col1, |
| group_col2 |
| HAVING COUNT(*) > 0 |
| AND group_col2 > 5 |
| ORDER BY group_col1, |
| group_col2; |
| -- |
| +--------------------+--------------------+--------------------+ |
| |COUNT(*) |group_col1 |group_col2 | |
| +--------------------+--------------------+--------------------+ |
| | 1| 3| 6| |
| | 1| 3| 7| |
| | 2| 4| 8| |
| | 1| 4| 9| |
| | 1| 5| 10| |
| | 1| 5| 11| |
| +--------------------+--------------------+--------------------+ |
| == |
| |
| |
| CREATE TABLE dates (value DATETIME); |
| INSERT INTO dates VALUES (DATETIME '2016-01-02 10:20:30'); |
| INSERT INTO dates VALUES (DATETIME '2016-02-03 11:21:31'); |
| INSERT INTO dates VALUES (DATETIME '2016-03-04 12:22:32'); |
| |
| SELECT EXTRACT(YEAR FROM value) * 10000 + |
| EXTRACT(MONTH FROM value) * 100 + |
| EXTRACT(DAY FROM value) AS date_digits |
| FROM dates; |
| -- |
| +--------------------+ |
| |date_digits | |
| +--------------------+ |
| | 20160102| |
| | 20160203| |
| | 20160304| |
| +--------------------+ |
| == |
| |
| SELECT SUM(EXTRACT(SECOND FROM value)) |
| FROM dates; |
| -- |
| +-------------------------------+ |
| |SUM(EXTRACT(SECOND FROM value))| |
| +-------------------------------+ |
| | 93| |
| +-------------------------------+ |
| == |
| |
| SELECT EXTRACT(HOUR FROM value + INTERVAL '1 hour') |
| FROM dates; |
| -- |
| +-----------------------------------------------------+ |
| |EXTRACT(HOUR FROM (value+DatetimeInterval('1 hour')))| |
| +-----------------------------------------------------+ |
| | 11| |
| | 12| |
| | 13| |
| +-----------------------------------------------------+ |
| == |
| |
| SELECT i, |
| CASE i%2 |
| WHEN 1 THEN 'odd' |
| ELSE 'even' |
| END |
| FROM generate_series(1, 5) AS gs(i); |
| -- |
| +-----------+--------------------------------------------+ |
| |i |CASE (i%2) WHEN 1 THEN 'odd' ELSE 'even' END| |
| +-----------+--------------------------------------------+ |
| | 1| odd| |
| | 2| even| |
| | 3| odd| |
| | 4| even| |
| | 5| odd| |
| +-----------+--------------------------------------------+ |
| == |
| |
| SELECT SUM(CASE WHEN i < 4 THEN i |
| ELSE i * i |
| END) AS result |
| FROM generate_series(1, 5) AS gs(i); |
| -- |
| +--------------------+ |
| |result | |
| +--------------------+ |
| | 47| |
| +--------------------+ |
| == |
| |
| SELECT * |
| FROM generate_series(1, 3) AS gs1(i), |
| generate_series(1, 3) AS gs2(j) |
| WHERE CASE WHEN i < j THEN i |
| ELSE j |
| END > 1; |
| -- |
| +-----------+-----------+ |
| |i |j | |
| +-----------+-----------+ |
| | 2| 2| |
| | 2| 3| |
| | 3| 2| |
| | 3| 3| |
| +-----------+-----------+ |
| == |
| |
| SELECT i AS odd |
| FROM generate_series(0, 10, 1) AS gs1(i) |
| WHERE |
| NOT EXISTS ( |
| SELECT * |
| FROM generate_series(0, 10, 2) AS gs2(even) |
| WHERE i = even |
| ); |
| -- |
| +-----------+ |
| |odd | |
| +-----------+ |
| | 1| |
| | 3| |
| | 5| |
| | 7| |
| | 9| |
| +-----------+ |
| == |
| |
| SELECT i |
| FROM generate_series(0, 100, 3) AS gs1(i) |
| WHERE |
| EXISTS ( |
| SELECT * |
| FROM generate_series(0, 100, 5) AS gs2(i) |
| WHERE gs1.i = gs2.i |
| ) |
| AND NOT EXISTS ( |
| SELECT * |
| FROM generate_series(0, 100, 10) AS gs3(i) |
| WHERE gs1.i = gs3.i |
| ) |
| AND (i < 40 OR i > 60); |
| -- |
| +-----------+ |
| |i | |
| +-----------+ |
| | 15| |
| | 75| |
| +-----------+ |
| == |
| |
| # SUBSTRING function |
| SELECT char_col, |
| SUBSTRING(char_col FROM 1 FOR 2) AS negative_value |
| FROM test |
| WHERE SUBSTRING(char_col FROM 1 FOR 1) = '-' |
| -- |
| +--------------------+--------------+ |
| |char_col |negative_value| |
| +--------------------+--------------+ |
| | -1 1.000000| -1| |
| | -3 1.732051| -3| |
| | -5 2.236068| -5| |
| | -7 2.645751| -7| |
| | -9 3.000000| -9| |
| | -11 3.316625| -1| |
| | -13 3.605551| -1| |
| | -15 3.872983| -1| |
| | -17 4.123106| -1| |
| | -19 4.358899| -1| |
| | -21 4.582576| -2| |
| | -23 4.795832| -2| |
| +--------------------+--------------+ |
| == |
| |
| # IN predicate |
| SELECT * |
| FROM generate_series(1, 5) AS gs(i) |
| WHERE i IN (2, 4); |
| -- |
| +-----------+ |
| |i | |
| +-----------+ |
| | 2| |
| | 4| |
| +-----------+ |
| == |
| |
| SELECT * |
| FROM generate_series(1, 5) AS gs(i) |
| WHERE i NOT IN (2, 4); |
| -- |
| +-----------+ |
| |i | |
| +-----------+ |
| | 1| |
| | 3| |
| | 5| |
| +-----------+ |
| == |
| |
| SELECT * |
| FROM generate_series(1, 5) AS gs(i) |
| WHERE i NOT IN (i*i-6, CASE WHEN i < 3 THEN 1 ELSE 4 END); |
| -- |
| +-----------+ |
| |i | |
| +-----------+ |
| | 2| |
| | 5| |
| +-----------+ |
| == |
| |
| SELECT * |
| FROM generate_series(1, 10) AS gs(i) |
| WHERE i NOT IN ( |
| SELECT * |
| FROM generate_series(2, 10, 2) |
| ); |
| -- |
| +-----------+ |
| |i | |
| +-----------+ |
| | 1| |
| | 3| |
| | 5| |
| | 7| |
| | 9| |
| +-----------+ |
| == |
| |
| # Scalar subquery expression |
| SELECT * |
| FROM test |
| WHERE double_col < 0 |
| AND long_col > (SELECT AVG(long_col) FROM test); |
| -- |
| +-----------+--------------------+---------------+------------------------+--------------------+ |
| |int_col |long_col |float_col |double_col |char_col | |
| +-----------+--------------------+---------------+------------------------+--------------------+ |
| | -15| 225| 3.87298346| -58.094750193111253| -15 3.872983| |
| | -17| 289| 4.12310553| -70.092795635500224| -17 4.123106| |
| | -19| 361| 4.35889912| -82.81907992727281| -19 4.358899| |
| | -21| 441| 4.5825758| -96.234089594072643| -21 4.582576| |
| | -23| 529| 4.79583168| -110.30412503619254| -23 4.795832| |
| +-----------+--------------------+---------------+------------------------+--------------------+ |
| == |
| |
| SELECT i + ( |
| SELECT SUM(j) |
| FROM generate_series(1, 10) AS gs(j)) |
| FROM generate_series(1, 5) AS gs(i); |
| -- |
| +----------------------+ |
| |(i+SubqueryExpression)| |
| +----------------------+ |
| | 56| |
| | 57| |
| | 58| |
| | 59| |
| | 60| |
| +----------------------+ |
| == |
| |
| SELECT i + ( |
| SELECT SUM(j) |
| FROM generate_series(1, 10) AS gs1(j), |
| generate_series(1, 10) AS gs2(k) |
| WHERE i = k AND j <= k) |
| FROM generate_series(1, 5) AS gs(i); |
| -- |
| +----------------------+ |
| |(i+SubqueryExpression)| |
| +----------------------+ |
| | 2| |
| | 5| |
| | 9| |
| | 14| |
| | 20| |
| +----------------------+ |
| == |
| |
| # This query is to test that the output columns have the correct alias name as specified. |
| SELECT * |
| FROM ( |
| SELECT i, SUM(i) AS sum |
| FROM generate_series(1, 2) AS gs(i) |
| GROUP BY i |
| ) t1 JOIN ( |
| SELECT j, AVG(j) AS avg |
| FROM generate_series(1, 2) AS gs(j) |
| GROUP BY j |
| ) t2 ON i = j; |
| -- |
| +-----------+--------------------+-----------+------------------------+ |
| |i |sum |j |avg | |
| +-----------+--------------------+-----------+------------------------+ |
| | 1| 1| 1| 1| |
| | 2| 2| 2| 2| |
| +-----------+--------------------+-----------+------------------------+ |
| == |
| |
| # Same shared subplan referenced multiple times. |
| WITH t(x, y) AS ( |
| SELECT i % 5, i |
| FROM generate_series(1, 20) AS g(i) |
| ) |
| SELECT * |
| FROM t |
| WHERE t.y = ( |
| SELECT MAX(y) |
| FROM t t1 |
| WHERE t.x = t1.x |
| ) |
| ORDER BY x; |
| -- |
| +-----------+-----------+ |
| |x |y | |
| +-----------+-----------+ |
| | 0| 20| |
| | 1| 16| |
| | 2| 17| |
| | 3| 18| |
| | 4| 19| |
| +-----------+-----------+ |
| == |
| |
| SELECT COUNT(*) |
| FROM test, |
| (SELECT AVG(long_col) a FROM test) subquery |
| WHERE double_col < 0 |
| AND long_col > subquery.a; |
| -- |
| +--------------------+ |
| |COUNT(*) | |
| +--------------------+ |
| | 5| |
| +--------------------+ |
| == |
| |
| # Window Aggregation Test. |
| SELECT char_col, long_col, avg(long_col) OVER w FROM test |
| WINDOW w AS |
| (ORDER BY char_col DESC NULLS LAST |
| ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW); |
| -- |
| +--------------------+--------------------+------------------------+ |
| |char_col |long_col |avg(long_col) | |
| +--------------------+--------------------+------------------------+ |
| | 8 2.828427| 64| 64| |
| | 6 2.449490| 36| 50| |
| | 4 2.000000| 16| 38.666666666666664| |
| | 24 4.898979| 576| 173| |
| | 22 4.690416| 484| 235.19999999999999| |
| | 20 4.472136| 400| 262.66666666666669| |
| | 2 1.414214| 4| 225.71428571428572| |
| | 18 4.242641| 324| 238| |
| | 16 4.000000| 256| 240| |
| | 14 3.741657| 196| 235.59999999999999| |
| | 12 3.464102| 144| 227.27272727272728| |
| | 10 3.162278| 100| 216.66666666666666| |
| | 0 0.000000| 0| 200| |
| | -9 3.000000| 81| 191.5| |
| | -7 2.645751| 49| 182| |
| | -5 2.236068| 25| 172.1875| |
| | -3 1.732051| 9| 162.58823529411765| |
| | -23 4.795832| 529| 182.94444444444446| |
| | -21 4.582576| 441| 196.52631578947367| |
| | -19 4.358899| 361| 204.75| |
| | -17 4.123106| 289| 208.76190476190476| |
| | -15 3.872983| 225| 209.5| |
| | -13 3.605551| 169| 207.7391304347826| |
| | -11 3.316625| 121| 204.125| |
| | -1 1.000000| 1| 196| |
| +--------------------+--------------------+------------------------+ |
| == |
| |
| SELECT long_col, int_col, avg(int_col) OVER w FROM test |
| WINDOW w AS |
| (ORDER BY long_col DESC NULLS LAST |
| ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING); |
| -- |
| +--------------------+-----------+------------------------+ |
| |long_col |int_col |avg(int_col) | |
| +--------------------+-----------+------------------------+ |
| | 576| 24| 7.666666666666667| |
| | 529| -23| 0.5| |
| | 484| 22| 0.5| |
| | 441| -21| -10.25| |
| | 400| NULL| 0| |
| | 361| -19| -9.75| |
| | 324| 18| -0.5| |
| | 289| -17| -3.3999999999999999| |
| | 256| 16| 3.2000000000000002| |
| | 225| -15| -3| |
| | 196| 14| 2.7999999999999998| |
| | 169| -13| -2.6000000000000001| |
| | 144| 12| 0.5| |
| | 121| -11| -5.25| |
| | 100| NULL| 0| |
| | 81| -9| -4.75| |
| | 64| 8| -0.5| |
| | 49| -7| -1.3999999999999999| |
| | 36| 6| 1.2| |
| | 25| -5| -1| |
| | 16| 4| 0.80000000000000004| |
| | 9| -3| -0.59999999999999998| |
| | 4| 2| 0.5| |
| | 1| -1| -0.66666666666666663| |
| | 0| NULL| 0.5| |
| +--------------------+-----------+------------------------+ |
| == |
| |
| SELECT float_col, double_col, avg(double_col) OVER |
| (ORDER BY float_col DESC NULLS LAST, int_col ASC NULLS FIRST |
| RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING) |
| FROM test; |
| -- |
| +---------------+------------------------+------------------------+ |
| |float_col |double_col |avg(double_col) | |
| +---------------+------------------------+------------------------+ |
| | 4.89897966| 117.57550765359254| -5.2010907233390986| |
| | 4.79583168| -110.30412503619254| -3.3458568752518572| |
| | 4.69041586| 103.18914671611546| -3.3458568752518572| |
| | 4.5825758| -96.234089594072643| -4.2942570191393745| |
| | 4.47213602| NULL| -4.2942570191393745| |
| | 4.35889912| -82.81907992727281| -3.1771278735018194| |
| | 4.2426405| 76.367532368147124| -3.1771278735018194| |
| | 4.12310553| -70.092795635500224| -3.6217507631683268| |
| | 4| 64| -3.0100796703699935| |
| | 3.87298346| -58.094750193111253| -3.0100796703699935| |
| | 3.7416575| 52.38320341483518| -3.0100796703699935| |
| | 3.60555124| -46.872166581031856| -3.1193833079868254| |
| | 3.46410155| 41.569219381653056| -3.1193833079868254| |
| | 3.31662488| -36.4828726939094| -2.8361542397614437| |
| | 3.1622777| NULL| -2.8361542397614437| |
| | 3| -27| -2.7526926834086507| |
| | 2.82842708| 22.627416997969522| -8.4826069851706123| |
| | 2.64575124| -18.520259177452136| -9.0010404404476727| |
| | 2.44948983| 14.696938456699067| -4.1547599319129516| |
| | 2.23606801| -11.180339887498949| -4.2708832009567148| |
| | 2| 8| 0.11724429467951912| |
| | 1.73205078| -5.196152422706632| -4.7108157334609286| |
| | 1.41421354| 2.8284271247461903| -5.1226841602152344| |
| | 1| -1| -1.638218767582549| |
| | 0| NULL| 1.1580686755098886| |
| +---------------+------------------------+------------------------+ |
| == |
| |
| SELECT sum(avg(int_col) OVER w) FROM test |
| WINDOW w AS |
| (PARTITION BY char_col |
| ORDER BY long_col |
| ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW); |
| -- |
| +------------------------+ |
| |sum(avg(int_col)) | |
| +------------------------+ |
| | -18| |
| +------------------------+ |
