The :window-functions/offset (offset window functions) Metabase feature is currently disabled, as the default Metabase implementation is incompatible with ClickHouse; the tests showed that the generated queries are not valid:
clojure.lang.ExceptionInfo: Error executing query: Code: 63. DB::Exception: Aggregate function with name 'LAG' does not exists. In scope SELECT source.created_at AS created_at, SUM(source.total) AS sum, LAG(SUM(source.total), 1) OVER (ORDER BY source.created_at ASC) AS offset FROM (SELECT toStartOfYear(test_data.orders.created_at) AS created_at, test_data.orders.total AS total FROM test_data.orders) AS source GROUP BY source.created_at ORDER BY source.created_at ASC LIMIT 3. (UNKNOWN_AGGREGATE_FUNCTION) (version 24.3.1.2672 (official build))
, server ClickHouseNode [uri=http://localhost:8123/default, options={use_server_time_zone_for_dates=true,use_no_proxy=false,product_name=metabase/1.4.1}]@-2141897951
driver: :clickhouse
params: nil
sql: ["-- Metabase"
"SELECT"
" `source`.`created_at` AS `created_at`,"
" SUM(`source`.`total`) AS `sum`,"
" LAG(SUM(`source`.`total`), 1) OVER ("
" ORDER BY"
" `source`.`created_at` ASC"
" ) AS `offset`"
"FROM"
" ("
...]
type: :invalid-query
java.sql.SQLException: Code: 63. DB::Exception: Aggregate function with name 'LAG' does not exists. In scope SELECT source.created_at AS created_at, SUM(source.total) AS sum, LAG(SUM(source.total), 1) OVER (ORDER BY source.created_at ASC) AS offset FROM (SELECT toStartOfYear(test_data.orders.created_at) AS created_at, test_data.orders.total AS total FROM test_data.orders) AS source GROUP BY source.created_at ORDER BY source.created_at ASC LIMIT 3. (UNKNOWN_AGGREGATE_FUNCTION) (version 24.3.1.2672 (official build))
See the driver changelog for 0.50.0.
The
:window-functions/offset(offset window functions) Metabase feature is currently disabled, as the default Metabase implementation is incompatible with ClickHouse; the tests showed that the generated queries are not valid:See also: https://clickhouse.com/docs/en/sql-reference/window-functions#standard-window-functions (the lag/lead part)