<!--
By default, query results are flat and unsorted. To ease analysis, you can lay out results as a cross-tabulation or sort results by multiple fields.
-->默认情况下，查询结果是扁平且未排序的。为了帮助比较和分析数据，您可以：
- 根据一个或多个字段，按升序或降序对结果进行排序。
- 使用交叉表布局，根据您选择的维度对结果进行水平和垂直分组，并将聚合值（例如 COUNT 或 MAX）应用于行或列。
From the Layout menu, select whether to lay out the results as flat or crosstab:
- Flat —By default, query results appear flat, with all fields as separate columns, like a table.
- Crosstab —To help compare and analyze data, use a cross-tabulation—or crosstab—layout. Crosstab groups the results horizontally and vertically based on dimensions you choose. To further help summarize the data, you can also apply calculations—such as COUNT or MAX—to values.
例如，默认情况下，查询结果以扁平表格的形式提供销售数据：
| 地区 | 销售额 | 财政年度 |
|---|---|---|
| 北 | 100 | 2020 |
| 北 | 50 | 2020 |
| 南 | 100 | 2019 |
| 南 | 250 | 2020 |
| 东方 | 200 | 2019 |
| 东方 | 50 | 2019 |
| 西方 | 100 | 2019 |
| 西方 | 100 | 2020 |
通过交叉表结果，您可以整理和汇总数据，以比较每个地区在每个财政年度的总销售额：
| 地区 | 2019 | 2020 |
|---|---|---|
| 东方 | 250 | 0 |
| 北 | 0 | 150 |
| 南 | 100 | 250 |
| 西方 | 100 | 100 |
列出交叉表结果
为了便于分析分类数据，您可以将结果以交叉表的形式呈现，行、列和值分别位于 Builder 或 SQL 中。
- 在查询语句中，添加要包含在结果中的字段：
- 在 Builder中，将源字段和计算 添加到 Fields 选项卡。
- 在 SQL中，编写查询的 SQL，并验证是否出现语法错误。对于
SELECT语句中的每个字段，使用AS语句在交叉表布局中指定其标题，即使标题与字段名称相同。例如，SELECT field_name1 AS "alias", field_name2 AS "field_name2" FROM source_name。
- 在 “交叉表 ”面板中，选择 “启用交叉表” ，并指定如何在结果中显示字段
：- 对于要垂直显示的值 ，例如我们示例中的 区域 ，请将其字段保留在行下，并选择是按升序还是降序对其数据进行排序。
- 对于要水平显示的值 ，例如我们示例中的财政年度 ，请将其字段从行 移动到 列，并选择是按升序还是降序对其数据进行排序。
- 要对比较中的值应用计算，请将其字段从行 移动到值，然后选择要使用的聚合，例如 DISTINCTCOUNT 或 SUM。要将值显示为总计，请在 总计中选择它应该显示在行还是列中。
注： 要从结果中删除一行，请从查询中删除其对应的字段。
- 要预览交叉表布局中的结果，请单击 运行查询 。
- 点击 保存 。
<!--
- In the query, add the source fields and calculations to include in the results.
-
From the toolbar, select Layout
, Crosstab.
-
On the Fields tab, specify how to display the
fields in the results:
- For values to display vertically, such as Region in our example, leave its field under Rows.
- For values to display horizontally, such as Fiscal Year in our example, move its field from Rows to Columns.
- To apply a calculation to a value within the comparison, move its field from Rows to Values, and select the formula to use such as DISTINCTCOUNT or SUM.
Note: To remove a row from the results, delete its field from the query.
- To view the results in the crosstab layout, click Run Query .
- Click Save .
- In the query, add the source fields and calculations to include in the results.
-
From the Layout
panel, select Enable Crosstab, and specify how
to display the fields in the results:
- For values to display vertically, such as Region in our example, leave its field under Rows, and select whether to sort its data in ascending or descending order.
- For values to display horizontally, such as Fiscal Year in our example, move its field from Rows to Columns.
- To apply a calculation to a value within the comparison, move its field from Rows to Values, and select the aggregation to use such as DISTINCTCOUNT or SUM. To display the value as a grand total, in Totals, select whether it should appear for rows or columns.
Note: To remove a row from the results, delete its field from the query.
- To preview the results in the crosstab layout, click Run Query .
- Click Save .
- In the editor, compose the query's SQL, and verify no syntax errors occur.
-
For each field in
the
SELECTstatement, use anASstatement to specify its header in the crosstab layout, including if the same as the field's name. For example,SELECT field_name1 AS "alias", field_name2 AS "field_name2" FROM source_name. -
From the Layout
panel, select Enable Crosstab, and specify how
to display the fields in the results:
- For values to display vertically, such as Region in our example, leave its field under Rows.
- For values to display horizontally, such as Fiscal Year in our example, move its field from Rows to Columns.
- To apply a calculation to a value within the comparison, move its field from Rows to Values, and select the formula to use such as DISTINCTCOUNT or SUM. To display the value as a grand total, in Totals, select whether it should appear for rows or columns.
Note: To remove a row from the results, delete its field from the query.
- To preview the results in the crosstab layout, click Run Query .
- Click Save .
Include grand totals in crosstab results
To help summarize values in crosstab results, you can include grand totals for columns or rows in either Builder or SQL:
- When you set up the crosstab layout, drag the field with the values to calculate from Rows to Values, and select the aggregation to use, such as DISTINCTCOUNT or SUM.
-
From the toolbar, click Totals, and select whether to show
the grand totals for rows or columns.
<!-- - Click Save .
平铺结果
<!--By default, query results appear in a flat layout. To return to a flat layout after setting up crosstab results, select Layout , Flat from the toolbar.
默认情况下，查询结果以扁平化布局显示。要将交叉表结果返回到扁平布局，请从“布局”面板中取消选择 “启用交叉表 ” 。
排序结果
依预设不会排序显示的查询结果。在 Builder 或 SQL中，您可以根据一个或多个字段按升序或降序对结果进行排序。
- 在 排序 选项卡上，将每个要排序的字段从 未分类 移动到 已排序，并选择是按升序 还是降序 对其数据进行排序。
- 如果按多个字段排序，请在 已排序下，按数据排序的顺序排列它们。例如，您可能希望先按一个字段升序排序，然后再按另一个字段降序排序。
- 点击 保存 。
在编写查询的 SQL 语句时，使用 ORDER BY 关键字指定要按升序 (ASC)或降序 (DESC)排序的字段 ，例如 ORDER BY field_header ASC。
要按多个字段排序，请用逗号分隔每个字段，例如 ORDER BY field_header1 ASC, field_header2 DESC。