SELECT

説明

主にSelect構文の使用方法を紹介します

文法:

SELECT
    [hint_statement, ...]
    [ALL | DISTINCT | DISTINCTROW | ALL EXCEPT ( col_name1 [, col_name2, col_name3, ...] )]
    select_expr [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
      [TABLET tabletid_list]
      [TABLESAMPLE sample_value [ROWS | PERCENT]
        [REPEATABLE pos_seek]]
    [WHERE where_condition]
    [GROUP BY [GROUPING SETS | ROLLUP | CUBE] {col_name | expr | position}]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [INTO OUTFILE 'file_name']

1. 構文の説明:

   1. select_expr, ... 結果で取得および表示される列、エイリアスを使用する場合、asはオプションです。

   2. select_expr, ... 取得対象のテーブル（1つまたは複数のテーブル（サブクエリによって生成された一時テーブルを含む））

   3. where_definition 取得条件（式）、WHERE句がある場合、条件によって行データがフィルタリングされます。where_conditionは、選択される各行について真と評価される式です。WHERE句がない場合、ステートメントはすべての行を選択します。WHERE式では、集約関数以外のMySQLでサポートされている任意の関数および演算子を使用できます

   4. ALL | DISTINCT：結果セットを更新、allはすべて、distinct/distinctrowは重複する列を更新、デフォルトはallです

   5. ALL EXCEPT：完全（all）結果セットでフィルタリング、exceptは完全結果セットから除外される1つまたは複数の列の名前を指定します。一致するすべての列名は出力で無視されます。

   この機能はApache Doris 1.2バージョン以降でサポートされています

   6. INTO OUTFILE 'file_name'：結果を新しいファイル（以前は存在しなかった）に保存、違いは保存形式にあります。

   7. Group by having：結果セットをグループ化し、havingが現れた場合にgroup byの結果を更新します。Grouping Sets、Rollup、Cubeはgroup byの拡張です、詳細についてはGROUPING SETS DESIGNを参照してください。

   8. Order by：最終結果をソート、Order byは1つまたは複数の列のサイズを比較して結果セットをソートします。

      Order byは時間がかかりリソース集約的な操作です。すべてのデータがソート前に1つのノードに送信される必要があり、ソート操作は非ソート操作よりも多くのメモリを必要とするためです。

      上位N個のソートされた結果を返す必要がある場合は、LIMIT句を使用する必要があります。メモリ使用量を制限するため、ユーザーがLIMIT句を指定しない場合、デフォルトで最初の65535個のソートされた結果が返されます。

   9. Limit n：出力結果の行数を制限、limit m,nはm行目からn個のレコードを出力することを意味します。limit m,nを使用する前にorder byを使用する必要があります。そうしないと実行するたびにデータが一致しない可能性があります。

   10. Having句はテーブル内の行データをフィルタリングするのではなく、集約関数によって生成された結果をフィルタリングします。

       通常havingは集約関数（例：COUNT(), SUM(), AVG(), MIN(), MAX()）およびgroup by句と共に使用されます。

   11. SELECTはtable_reference内のテーブル名の後にパーティションまたはサブパーティション（またはその両方）のリストを含むPARTITIONを使用した明示的なパーティション選択をサポートします

   12. [TABLET tids] TABLESAMPLE n [ROWS | PERCENT] [REPEATABLE seek]：FROM句内のテーブルから読み取られる行数を制限、指定された行数またはパーセンテージに従ってテーブルから擬似ランダムに複数のTabletsを選択し、REPEATABLEでシードの数を指定して選択されたサンプルを再度返します。また、TableIDを手動で指定することもできます。これはOLAPテーブルでのみ使用できることに注意してください。

   13. hint_statement：selectlistの前のhintは、望ましい実行プランを得るためにオプティマイザーの動作に影響を与えるヒントが使用できることを示します。詳細についてはjoinHint使用ドキュメントを参照してください

構文の制約:

1. SELECTは、任意のテーブルを参照せずに計算された行を取得するためにも使用できます。
2. すべての句は上記の形式に従って厳密に順序付けられる必要があり、HAVING句はGROUP BY句の後、ORDER BY句の前に配置される必要があります。
3. エイリアスキーワードASはオプションです。エイリアスはgroup by、order by、havingで使用できます
4. Where句：WHISTATEMENTが実行されて、GROUP BY セクションに含まれるべき行が決定され、HAVINGは結果セット内のどの行を使用するべきかを決定するために使用されます。
5. HAVING句は集計関数を参照できますが、WHERE句はcount、sum、max、min、avgなどを参照できません。同時に、where句は集計関数以外の他の関数を参照できます。列エイリアスはWhere句で条件を定義するために使用できません。
6. Group byの後にwith rollupを続けると、結果を1回または複数回カウントできます。

Join クエリ:

DorisはJOIN構文をサポートします

JOIN
table_references:
    table_reference [, table_reference] …
table_reference:
    table_factor
  | join_table
table_factor:
    tbl_name [[AS] alias]
        [{USE|IGNORE|FORCE} INDEX (key_list)]
  | ( table_references )
  | { OJ table_reference LEFT OUTER JOIN table_reference
        ON conditional_expr }
join_table:
    table_reference [INNER | CROSS] JOIN table_factor [join_condition]
  | table_reference LEFT [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [LEFT [OUTER]] JOIN table_factor
  | table_reference RIGHT [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [RIGHT [OUTER]] JOIN table_factor
join_condition:
    ON conditional_expr

UNION文法:

SELECT ...
UNION [ALL| DISTINCT] SELECT ......
[UNION [ALL| DISTINCT] SELECT ...]

UNIONは複数のSELECT文の結果を単一の結果セットに結合するために使用されます。

最初のSELECT文の列名が返される結果の列名として使用されます。各SELECT文の対応する位置にリストされた選択された列は、同じデータ型である必要があります。（例えば、最初の文で選択された最初の列は、他の文で選択された最初の列と同じ型である必要があります。）

UNIONのデフォルトの動作は、結果から重複する行を削除することです。オプションのDISTINCTキーワードは、重複する行の削除も指定するため、デフォルト以外の効果はありません。オプションのALLキーワードを使用すると、重複する行の削除は行われず、結果にはすべてのSELECT文のすべての一致する行が含まれます。

WITH文:

共通テーブル式を指定するには、1つ以上のカンマ区切りの句でWITH句を使用します。各副句は結果セットを生成するサブクエリを提供し、名前をサブクエリに関連付けます。次の例では、cte1とcte2という名前のCTEでWITH句を定義し、それらの最上位のSELECTの下でWITH句を参照しています：

WITH
  cte1 AS (SELECT a，b FROM table1),
  cte2 AS (SELECT c，d FROM table2)
SELECT b，d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

WITH句を含むステートメントでは、各CTE名を参照して対応するCTE結果セットにアクセスできます。

CTE名は他のCTEで参照できるため、他のCTEに基づいてCTEを定義することが可能です。

再帰CTEは現在サポートされていません。

例

1. 年齢が18、20、25の学生の名前を照会する

   select Name from student where age in (18,20,25);

2. Example以外のすべて

   -- Query all information except the students' age
   select * except(age) from student; 

3. GROUP BY の例

   --Query the tb_book table, group by type, and find the average price of each type of book,
   select type,avg(price) from tb_book group by type;

4. DISTINCTの使用

   --Query the tb_book table to remove duplicate type data
   select distinct type from tb_book;

5. ORDER BY の例

   クエリ結果を昇順（デフォルト）または降順（DESC）でソートします。昇順では NULL が最初に、降順では NULL が最後になります

   --Query all records in the tb_book table, sort them in descending order by id, and display three records
   select * from tb_book order by id desc limit 3;

6. LIKE あいまい検索

   あいまい検索を実現でき、2つのワイルドカードがあります：%と_、%は1文字以上の文字にマッチし、_は1文字にマッチします

   --Find all books whose second character is h
   select * from tb_book where name like('_h%');

7. LIMITは結果行の数を制限します

   --1. Display 3 records in descending order
   select * from tb_book order by price desc limit 3;
   
   --2. Display 4 records from id=1
   select * from tb_book where id limit 1,4;

8. CONCAT 複数の列を結合

   --Combine name and price into a new string output
   select id,concat(name,":",price) as info,type from tb_book;

9. 関数と式の使用

   --Calculate the total price of various books in the tb_book table
   select sum(price) as total,type from tb_book group by type;
   --20% off price
   select *,(price * 0.8) as "20%" from tb_book;

10. UNION の例

    SELECT a FROM t1 WHERE a = 10 AND B = 1 ORDER by LIMIT 10
    UNION
    SELECT a FROM t2 WHERE a = 11 AND B = 2 ORDER by LIMIT 10;

11. WITH句の例

    WITH cte AS
    (
      SELECT 1 AS col1, 2 AS col2
      UNION ALL
      SELECT 3, 4
    )
    SELECT col1, col2 FROM cte;

12. JOIN の例

    SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
                     ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c)

と同等

```sql
SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
                 ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c)
```

13. INNER JOIN

```sql
SELECT t1.name, t2.salary
  FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;

SELECT t1.name, t2.salary
  FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name;
```

14. LEFT JOIN

```sql
SELECT left_tbl.*
  FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
  WHERE right_tbl.id IS NULL;
```

15. RIGHT JOIN

```sql
mysql> SELECT * FROM t1 RIGHT JOIN t2 ON (t1.a = t2.a);
+------+------+------+------+
| a    | b    | a    | c    |
+------+------+------+------+
|    2 | y    |    2 | z    |
| NULL | NULL |    3 | w    |
+------+------+------+------+
```

16. TABLESAMPLE

```sql
--Pseudo-randomly sample 1000 rows in t1. Note that several Tablets are actually selected according to the statistics of the table, and the total number of selected Tablet rows may be greater than 1000, so if you want to explicitly return 1000 rows, you need to add Limit.
SELECT * FROM t1 TABLET(10001) TABLESAMPLE(1000 ROWS) REPEATABLE 2 limit 1000;
```

Keywords

SELECT

ベストプラクティス

1. SELECT句に関する追加知識

   • select_exprには AS alias_name を使用してエイリアスを指定できます。エイリアスは式内でカラム名として使用され、GROUP BY、ORDER BY、またはHAVING句で使用できます。カラムのエイリアスを指定する際は、ASキーワードを使用することが良い習慣です。

   • FROMの後のtable_referencesは、クエリに参加する1つ以上のテーブルを示します。複数のテーブルがリストされている場合、JOIN操作が実行されます。そして指定された各テーブルに対して、エイリアスを定義できます

   • SELECTの後の選択されたカラムは、カラム名、カラムエイリアス、またはカラム位置を表す整数（1から開始）によってORDER INおよびGROUP BYで参照できます

     SELECT college, region, seed FROM tournament
       ORDER BY region, seed;
     
     SELECT college, region AS r, seed AS s FROM tournament
       ORDER BY r, s;
     
     SELECT college, region, seed FROM tournament
       ORDER BY 2, 3;

• ORDER BYがサブクエリに現れ、かつ外側のクエリにも適用される場合、最も外側のORDER BYが優先されます。

  • GROUP BYが使用される場合、グループ化されたカラムは自動的に昇順でソートされます（同じカラムに続くORDER BY文があるかのように）。自動ソートによるGROUP BYのオーバーヘッドを避けたい場合は、ORDER BY NULLを追加することで解決できます：

    SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;

• SELECT で ORDER BY や GROUP BY を使用して列をソートする際、サーバーは max_sort_length システム変数で指定された初期バイト数のみを使用して値をソートします。

  • HAVING 句は一般的に最後に適用され、結果セットが MySQL クライアントに返される直前に実行され、最適化されません。（LIMIT は HAVING の後に適用されます）

    SQL 標準では、HAVING は GROUP BY リスト内の列、または集約関数で使用される列を参照する必要があります。ただし、MySQL はこれを拡張し、HAVING が Select 句リスト内の列や外部サブクエリの列を参照することを許可しています。

    HAVING で参照される列が曖昧な場合、警告が生成されます。以下のステートメントでは、col2 が曖昧です：

    SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;

• WHERE を使うべき場所で HAVING を使わないよう注意してください。HAVING は GROUP BY と組み合わせて使用します。

  • HAVING 句は集約関数を参照できますが、WHERE では参照できません。

    SELECT user, MAX(salary) FROM users
      GROUP BY user HAVING MAX(salary) > 10;

• LIMIT句は、SELECT文によって返される行数を制限するために使用できます。LIMITには1つまたは2つの引数を指定でき、どちらも非負の整数である必要があります。

  /*Retrieve 6~15 rows in the result set*/
  SELECT * FROM tbl LIMIT 5,10;
  /*Then if you want to retrieve all rows after a certain offset is set, you can set a very large constant for the second parameter. The following query fetches all data from row 96 onwards */
  SELECT * FROM tbl LIMIT 95,18446744073709551615;
  /*If LIMIT has only one parameter, the parameter specifies the number of rows that should be retrieved, and the offset defaults to 0, that is, starting from the first row*/

• SELECT...INTOはクエリ結果をファイルに書き込むことを可能にします

2. SELECTキーワードの修飾子

   • 重複除去

     ALLとDISTINCT修飾子は、結果セット内の行を重複除去するかどうかを指定します（列であってはなりません）。

     ALLはデフォルトの修飾子で、要件を満たすすべての行を取得することを意味します。

     DISTINCTは重複する行を削除します。

3. サブクエリの主な利点

   • サブクエリは構造化クエリを可能にし、ステートメントの各部分を分離できます。
   • 一部の操作では複雑な結合と関連付けが必要です。サブクエリはこれらの操作を実行するための他の方法を提供します

4. クエリの高速化

   • データフィルタリング条件としてDorisのパーティションとバケットを可能な限り使用し、データスキャンの範囲を削減します
   • データフィルタ条件としてDorisのプレフィックスインデックスフィールドを最大限に活用し、クエリ速度を向上させます

5. UNION

   • unionキーワードのみの使用は、union disitnctの使用と同じ効果があります。重複除去作業はメモリ集約的であるため、union all操作を使用するクエリの方が高速で、メモリ消費量も少なくなります。ユーザーが返された結果セットに対してorder byとlimit操作を実行したい場合は、union操作をサブクエリ内に配置し、次にサブクエリからselectを行い、最後にサブクエリとorder byをサブクエリの外側に配置する必要があります。

     select * from (select age from student_01 union all select age from student_02) as t1
     order by age limit 4;
       
     +-------------+
     | age |
     +-------------+
     | 18 |
     | 19 |
     | 20 |
     | 21 |
     +-------------+
     4 rows in set (0.01 sec)

6. JOIN

   • 内部結合条件では、等価結合のサポートに加えて、非等価結合もサポートしています。パフォーマンス上の理由から、等価結合の使用を推奨します。
   • その他の結合では等価結合のみサポートしています