Results of 1 - 10 of about 13 for Amsterdam (0.001 sec.)
- PIVOT Statement 12704
- 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
...CREATE TABLE cities ( country VARCHAR, name VARCHAR, year INTEGER, population INTEGER ); INSERT INTO cities VALUES ('NL', 'Amsterdam', 2000, 1005), ('NL', 'Amsterdam', 2010, 1065), ('NL', 'Amsterdam', 2020, 1158), ('US', 'Seattle', 2000, 564), ('US', 'Sea..., '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 2010 608 US Seattle 2020 738 US New...ause 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 example, the sum aggregate is always op... - https://man.mtool.work/duckdb/2.0/sql/statements/pivot.html
- VALUES Clause 11806
- The VALUES clause is used to specify a fixed number of rows. The VALUES clause can be used as a stand-alone statement, as part of the FROM clause, or as input to an INSERT INTO statement. Examples Generate two rows and directly return them:
...VALUES ('Amsterdam', 1), ('London', 2); Generate two rows as part of a FROM clause, and rename the columns: SELECT * FROM (VALUES ('Amsterdam...', 1), ('London', 2)) cities(name, id); Generate two rows and insert them into a table: INSERT INTO cities VALUES ('Amsterdam', 1), ('London', 2); Create a table directly from a VALUES clause: CREATE TABLE cities AS SELECT * FROM (VALUES ('Amsterda... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/values.html
- query and query_table Functions 10240
- 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
...array_to_string(index, ', ') || ')) INTO NAME ' || name || ' VALUES ' || values ); WITH cities AS ( FROM ( VALUES ('NL', 'Amsterdam', '10', '12', '15'), ('US', 'New York', '100', '120', '150') ) _(country, city, '2000', '2010', '2020') ) SELECT * FROM st...ack('cities', ['country', 'city'], 'year', 'population'); country city year population NL Amsterdam 2000 10 NL Amsterdam 2010 12 NL Amsterdam 2020 15 US New York 2000 100 US New York 2010 120 US New York 2020 150... - https://man.mtool.work/duckdb/2.0/guides/sql_features/query_and_query_table_func...
- Set Operations 9589
- 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,
...ake 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, degrees INTEGER, date DATE); INSERT INTO weather VALUE...S ('Amsterdam', 10, '2022-10-14'), ('Seattle', 8, '2022-10-12'); SELECT * FROM capitals UNION BY NAME SELECT * FROM weather; city countr...y degrees date Seattle NULL 8 2022-10-12 Amsterdam NL NULL NULL Berlin Germany NULL NULL Amsterdam NULL 10 2022-10-14 UNION BY NAME follows set semantics (therefore it perfo... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/setops.html
- Writing JSON 9008
- The contents of tables or the result of queries can be written directly to a JSON file using the COPY statement. For example: CREATE TABLE cities AS FROM (VALUES ('Amsterdam', 1), ('London', 2)) cities(name, id); COPY cities TO 'cities.json
...'; This will result in cities.json with the following content: {"name":"Amsterdam","id":1} {"name":"London","id":2} See the COPY statement for more information. GeoJSON New This feature was introduced in... - https://man.mtool.work/duckdb/2.0/data/json/writing_json.html
- FROM and JOIN Clauses 8516
- The FROM clause specifies the source of the data on which the remainder of the query should operate. Logically, the FROM clause is where the query starts execution. The FROM clause can contain a single table, a combination of multiple table
...VARCHAR, iata VARCHAR); CREATE TABLE airport_names (iata VARCHAR, airport_name VARCHAR); INSERT INTO city_airport VALUES ('Amsterdam', 'AMS'), ('Rotterdam', 'RTM'), ('Eindhoven', 'EIN'), ('Groningen', 'GRQ'); INSERT INTO airport_names VALUES ('AMS', 'Amst...un: SELECT * FROM city_airport NATURAL JOIN airport_names; This produces the following result: city_name iata airport_name Amsterdam AMS Amsterdam Airport Schiphol Rotterdam RTM Rotterdam The Hague Airport Note that only rows where the same iata attribute...e is available in the airport_names table: SELECT * FROM city_airport SEMI JOIN airport_names USING (iata); city_name iata Amsterdam AMS Rotterdam RTM This query is equivalent to: SELECT * FROM city_airport WHERE iata IN (SELECT iata FROM airport_names);... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/from.html
- Pivot Internals 8269
- 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
...n_sum FROM cities GROUP 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] [564, 608, 738] US New York City [2000, 2010, 2020] [8...icalPivot 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 list-based aggregations shown above... - https://man.mtool.work/duckdb/2.0/internals/pivot.html
- Timestamp Issues 7530
- Timestamp with Time Zone Promotion Casts Working with time zones in SQL can be quite confusing at times. For example, when filtering to a date range, one might try the following query: SET timezone = 'America/Los_Angeles'; CREATE TABLE time
...ical supply and demand out to 2100 at hourly resolution, one can create the calendar table like so: SET timezone = 'Europe/Amsterdam'; CREATE OR REPLACE TABLE hourly AS SELECT ts, year::SMALLINT AS year, month::TINYINT AS month, day::TINYINT AS day, hour:... - https://man.mtool.work/duckdb/2.0/guides/sql_features/timestamps.html
- Timestamp with Time Zone Functions 7460
- This section describes functions and operators for examining and manipulating TIMESTAMP WITH TIME ZONE (or TIMESTAMPTZ ) values . See also the related TIMESTAMP functions . Time zone support is provided by the built-in ICU extension . In th
...e zone can be parsed from a string and cast to a representation in the local time zone: SELECT '2022-10-08 13:13:34 Europe/Amsterdam'::TIMESTAMPTZ::VARCHAR; 2022-10-08 04:13:34-07 -- the offset will differ based on your local time zone ICU Timestamp with...DB follows PostgreSQL behavior and adjusts the result forward to the next valid timestamp. Example: SET timezone = 'Europe/Amsterdam'; SELECT TIMESTAMPTZ '2025-03-29 02:30:00+01' + INTERVAL '1 day'; 2025-03-30 03:30:00+02... - https://man.mtool.work/duckdb/2.0/sql/functions/timestamptz.html
- Date Format Functions 7372
- The strftime and strptime functions can be used to convert between DATE / TIMESTAMP values and strings. This is often required when parsing CSV files, displaying output to the user or transferring information between programs. Because there
...decimal number. 2013, 2019 etc. %z Time offset from UTC in the form ±HH:MM, ±HHMM, or ±HH. -0700 %Z Time zone name. Europe/Amsterdam %% A literal % character. %... - https://man.mtool.work/duckdb/2.0/sql/functions/dateformat.html
DuckDB 2.0 Documentation / man.mtool.work