LIMIT 查询
查询可以包含一个 LIMIT 子句，采用以下两种方式限制其结果集：
-
从整个结果集返回行的子集。
-
在结果集上设置窗口分区并限制每个窗口中的行数。
限制查询结果集
查询使用具有 ORDER BY 的 LIMIT 子句时，它会从查询数据集返回特定行子集。Vertica 使用数据库查询排名流程 Top-K 优化高效地处理这些查询。Top-K 优化避免对整个数据集进行排序（以及可能的磁盘写入）来找到少量的行。这可以极大地提升查询性能。
例如，以下查询返回表 customer_dimension 中的前 20 行数据，按 number_of_employees 排序：
=> SELECT store_region, store_city||', '||store_state location, store_name, number_of_employees
FROM store.store_dimension ORDER BY number_of_employees DESC LIMIT 20;
store_region | location | store_name | number_of_employees
--------------+----------------------+------------+---------------------
East | Nashville, TN | Store141 | 50
East | Manchester, NH | Store225 | 50
East | Portsmouth, VA | Store169 | 50
SouthWest | Fort Collins, CO | Store116 | 50
SouthWest | Phoenix, AZ | Store232 | 50
South | Savannah, GA | Store201 | 50
South | Carrollton, TX | Store8 | 50
West | Rancho Cucamonga, CA | Store102 | 50
MidWest | Lansing, MI | Store105 | 50
West | Provo, UT | Store73 | 50
East | Washington, DC | Store180 | 49
MidWest | Sioux Falls, SD | Store45 | 49
NorthWest | Seattle, WA | Store241 | 49
SouthWest | Las Vegas, NV | Store104 | 49
West | El Monte, CA | Store100 | 49
SouthWest | Fort Collins, CO | Store20 | 49
East | Lowell, MA | Store57 | 48
SouthWest | Arvada, CO | Store188 | 48
MidWest | Joliet, IL | Store82 | 48
West | Berkeley, CA | Store248 | 48
(20 rows)
重要
如果 LIMIT 子句省略ORDER BY，则结果可能是不确定的。
限制窗口分区结果
您可以使用 LIMIT 对查询结果设置窗口分区，并限制每个窗口中返回的行数：
SELECT ... FROM dataset LIMIT num‑rows OVER ( PARTITION BY column‑expr‑x, ORDER BY column‑expr‑y [ASC | DESC] )
其中查询 dataset 在每个 column‑expr‑x 分区中返回具有最高或最低 column‑expr‑y 值的 num‑rows 个行。
例如，以下语句查询表 store.store_dimension 并包含一个指定窗口分区的 LIMIT 子句。在本例中，Vertica 按 store_region 对结果集进行分区，其中每个分区窗口显示一个区域中两个员工最少的商店：
=> SELECT store_region, store_city||', '||store_state location, store_name, number_of_employees FROM store.store_dimension
LIMIT 2 OVER (PARTITION BY store_region ORDER BY number_of_employees ASC);
store_region | location | store_name | number_of_employees
--------------+---------------------+------------+---------------------
West | Norwalk, CA | Store43 | 10
West | Lancaster, CA | Store95 | 11
East | Stamford, CT | Store219 | 12
East | New York, NY | Store122 | 12
SouthWest | North Las Vegas, NV | Store170 | 10
SouthWest | Phoenix, AZ | Store228 | 11
NorthWest | Bellevue, WA | Store200 | 19
NorthWest | Portland, OR | Store39 | 22
MidWest | South Bend, IN | Store134 | 10
MidWest | Evansville, IN | Store30 | 11
South | Mesquite, TX | Store124 | 10
South | Beaumont, TX | Store226 | 11
(12 rows)