Results of 1 - 10 of about 11 for country (0.007 sec.)
- PIVOT Statement 13531
- The PIVOT statement allows distinct values within a column to be separated into their own columns. The values within those new columns are calculated using an aggregate function on the subset of rows that match each distinct value. DuckDB i
...hey may not all be omitted. Example Data All examples use the dataset produced by the queries below: CREATE TABLE cities ( country VARCHAR, name VARCHAR, year INTEGER, population INTEGER ); INSERT INTO cities VALUES ('NL', 'Amsterdam', 2000, 1005), ('NL...w York City', 2000, 8015), ('US', 'New York City', 2010, 8175), ('US', 'New York City', 2020, 8772); SELECT * FROM cities; country name year population NL Amsterdam 2000 1005 NL Amsterdam 2010 1065 NL Amsterdam 2020 1158 US Seattle 2000 564 US Seattle 2...et pivot table. If the USING clause is not included, it defaults to count(*) . PIVOT cities ON year USING sum(population); country name 2000 2010 2020 NL Amsterdam 1005 1065 1158 US Seattle 564 608 738 US New York City 8015 8175 8772 In the above exampl... - https://man.mtool.work/duckdb/2.0/sql/statements/pivot.html
- UPDATE Statement 10722
- The UPDATE statement modifies the values of rows in a table. Examples For every row where i is NULL , set the value to 0 instead: UPDATE tbl SET i = 0 WHERE i IS NULL; Set all values of i to 1 and all values of j to 2: UPDATE tbl SET i = 1,
...use the FROM clause and express joins via the WHERE clause. For example: CREATE TABLE city (name VARCHAR, revenue BIGINT, country_code VARCHAR); CREATE TABLE country (code VARCHAR, name VARCHAR); INSERT INTO city VALUES ('Paris', 700, 'FR'), ('Lyon', 2...00, 'FR'), ('Brussels', 400, 'BE'); INSERT INTO country VALUES ('FR', 'France'), ('BE', 'Belgium'); To increase the revenue of all cities in France, join the city and the country...tables, and filter on the latter: UPDATE city SET revenue = revenue + 100 FROM country WHERE city.country_code = country.code AND country.name = 'France'; SELECT * FROM city; name revenue country_code Paris 80... - https://man.mtool.work/duckdb/2.0/sql/statements/update.html
- GROUPING SETS 9762
- GROUPING SETS , ROLLUP and CUBE can be used in the GROUP BY clause to perform a grouping over multiple dimensions within the same query. Note that this syntax is not compatible with GROUP BY ALL . Examples Compute the average income along t
...sily produce commonly used grouping sets. The ROLLUP clause will produce all “sub-groups” of a grouping set, e.g., ROLLUP (country, city, zip) produces the grouping sets (country, city, zip), (country, city), (country), () . This can be useful for produ...n is the amount of terms in the ROLLUP clause. CUBE produces grouping sets for all combinations of the inputs, e.g., CUBE (country, city, zip) will produce (country, city, zip), (country, city), (country, zip), (city, zip), (country), (city), (zip), ()... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/grouping_sets.html
- Pivot Internals 8890
- PIVOT Pivoting is implemented as a combination of SQL query re-writing and a dedicated PhysicalPivot operator for higher performance. Each PIVOT is implemented as set of aggregations into lists and then the dedicated PhysicalPivot operator
...ities ORDER BY year ); PIVOT cities ON year IN __pivot_enum_0_0 USING sum(population); and finally translated into: SELECT country, name, list(year), list(population_sum) FROM ( SELECT country, name, year, sum(population) AS population_sum FROM cities G...ROUP BY ALL ) GROUP BY ALL; This produces the result: country name list("year") list(population_sum) NL Amsterdam [2000, 2010, 2020] [1005, 1065, 1158] US Seattle [2000, 2010, 2020] [5...20] [8015, 8175, 8772] The PhysicalPivot operator converts those lists into column names and values to return this result: country name 2000 2010 2020 NL Amsterdam 1005 1065 1158 US Seattle 564 608 738 US New York City 8015 8175 8772 The rewrite into li... - https://man.mtool.work/duckdb/2.0/internals/pivot.html
- query and query_table Functions 8641
- The query_table and query functions enable powerful and more dynamic SQL. The query_table function returns the table whose name is specified by its string argument; the query function returns the table obtained by executing the query specif
...alues ); WITH cities AS ( FROM ( VALUES ('NL', 'Amsterdam', '10', '12', '15'), ('US', 'New York', '100', '120', '150') ) _(country, city, '2000', '2010', '2020') ) SELECT * FROM stack('cities', ['country', 'city'], 'year', 'population'); country city ye... - https://man.mtool.work/duckdb/2.0/guides/sql_features/query_and_query_table_func...
- Set Operations 7966
- Set operations allow queries to be combined according to set operation semantics . Set operations refer to the UNION [ALL] , INTERSECT [ALL] and EXCEPT [ALL] clauses. The vanilla variants use set semantics, i.e., they eliminate duplicates,
...e filled with NULL values for the other query. Take the following tables for example: CREATE TABLE capitals (city VARCHAR, country VARCHAR); INSERT INTO capitals VALUES ('Amsterdam', 'NL'), ('Berlin', 'Germany'); CREATE TABLE weather (city VARCHAR, degr...terdam', 10, '2022-10-14'), ('Seattle', 8, '2022-10-12'); SELECT * FROM capitals UNION BY NAME SELECT * FROM weather; city country degrees date Seattle NULL 8 2022-10-12 Amsterdam NL NULL NULL Berlin Germany NULL NULL Amsterdam NULL 10 2022-10-14 UNION... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/setops.html
- SELECT Clause 7877
- The SELECT clause specifies the list of columns that will be returned by the query. While it appears first in the clause, logically the expressions here are executed only at the end. The SELECT clause can contain arbitrary expressions that
...operation. Therefore, only use DISTINCT if necessary. DISTINCT ON Clause Select only the highest population city for each country: SELECT DISTINCT ON(country) city, population FROM cities ORDER BY population DESC; The DISTINCT ON clause returns only on... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/select.html
- Writing to Iceberg 7770
- The DuckDB iceberg extension supports writing to Iceberg tables when an Iceberg catalog is attached. Writing to a table read directly from storage with iceberg_scan is not supported: the catalog is required to commit the new table metadata.
...is is useful for prefix-based partitioning. CREATE TABLE iceberg_catalog.default.events ( event_id BIGINT, user_id BIGINT, country VARCHAR, event_time TIMESTAMP ) PARTITIONED BY (day(event_time), bucket(16, user_id), truncate(2, country)); The partition... - https://man.mtool.work/duckdb/2.0/core_extensions/iceberg/writing_to_iceberg.htm...
- Sharing Macros 7681
- DuckDB has a powerful macro mechanism that allows creating shorthands for common tasks. Sharing a Scalar Macro First, we define a macro that pretty-prints a non-negative integer as a short string with thousands, millions, and billions (with
...31901247247513112906072 name_long = 126079956280724674884063510870679874110 slug = 286823566458906756841161390341104965945 country = 143068442936912051858689770843609587944 type = 5665662315470785456147400604088879751 geo_lat = 1606081161352518212591265... - https://man.mtool.work/duckdb/2.0/guides/snippets/sharing_macros.html
- SQLite Extension 7486
- The SQLite extension allows DuckDB to directly read and write data from a SQLite database file. The data can be queried directly from the underlying SQLite tables. Data can be loaded from SQLite tables into DuckDB tables, or vice versa. Ins
...ying data is read directly from the SQLite tables in the file at query time. SHOW TABLES; name actor address category city country customer customer_list film film_actor film_category film_list film_text inventory language payment rental sales_by_film_c... - https://man.mtool.work/duckdb/2.0/core_extensions/sqlite.html
DuckDB 2.0 Documentation / man.mtool.work