Function timeSeriesClickHouseSQL

runtime/queries/column_timeseries.go:241–305

(timeRange *runtimev1.TimeSeriesTimeRange, q *ColumnTimeseries, temporaryTableName, tsAlias, timezone string, dialect drivers.Dialect)

}

func timeSeriesClickHouseSQL(timeRange *runtimev1.TimeSeriesTimeRange, q *ColumnTimeseries, temporaryTableName, tsAlias, timezone string, dialect drivers.Dialect) (string, []any) {
	dateTruncSpecifier := dialect.ConvertToDateTruncSpecifier(timeRange.Interval)
	measures := normaliseMeasures(q.Measures, q.Pixels != 0)
	filter := ""

	var args []any
	var timeSQL, colSQL, unit string
	var offset uint32
	if timeRange.Interval == runtimev1.TimeGrain_TIME_GRAIN_WEEK && q.FirstDayOfWeek > 1 {
		offset = 8 - q.FirstDayOfWeek
		unit = "day"
	} else if timeRange.Interval == runtimev1.TimeGrain_TIME_GRAIN_YEAR && q.FirstMonthOfYear > 1 {
		offset = 13 - q.FirstMonthOfYear
		unit = "month"
	} else {
		unit = "day" // never mind since offset is zero
	}
	timeSQL = `date_sub(` + unit + `, ?, date_trunc(?, date_add(` + unit + `, ?, toTimeZone(?::DATETIME64, ?))))`
	// start and end are not null else we would have an empty time range but column can still have null values
	colSQL = `date_sub(` + unit + `, ?, date_trunc(?, date_add(` + unit + `, ?, toTimeZone(` + dialect.EscapeIdentifier(q.TimestampColumnName) + `::Nullable(DATETIME64), ?))))`
	// nolint
	args = append(args, offset, dateTruncSpecifier, offset, timeRange.Start.AsTime(), timezone) // compute start
	args = append(args, offset, dateTruncSpecifier, offset, timeRange.End.AsTime(), timezone)   // compute end
	args = append(args, offset, dateTruncSpecifier, offset, timeRange.Start.AsTime(), timezone) // compute start again to generate series
	args = append(args, offset, dateTruncSpecifier, offset, timezone)                           // convert column
	args = append(args, timezone)

	return `CREATE TEMPORARY TABLE ` + temporaryTableName + ` AS (
			WITH time_range AS
			(
				SELECT ` + timeSQL + ` AS start, 
					` + timeSQL + ` AS end,
					date_diff(` + dateTruncSpecifier + `, start, end) AS interval 
			),
			number_range AS (
				SELECT 
					arrayJoin(range(interval::UInt64)) AS number 
				FROM time_range
			),
			-- generate a time series column that has the intended range
			template AS (
				SELECT ` + timeSQL + ` AS start, 
					 date_add(` + dateTruncSpecifier + `, number, start) AS ` + tsAlias + ` 
				FROM number_range
			),
			-- transform the original data, and optionally sample it.
			series AS (
				SELECT
					` + colSQL + ` AS ` + tsAlias + `,` + getExpressionColumnsFromMeasures(dialect, measures) + `
				FROM ` + dialect.EscapeTable(q.Database, q.DatabaseSchema, q.TableName) + ` ` + filter + `
				GROUP BY ` + tsAlias + ` ORDER BY ` + tsAlias + `
			)
			-- an additional grouping is required for time zone DST (see unit tests for examples)
			SELECT ` + tsAlias + `,` + getCoalesceStatementsMeasuresLast(dialect, measures) + ` FROM (
				-- join the transformed data with the generated time series column,
				-- coalescing the first value to get the 0-default when the rolled up data
				-- does not have that value.
				SELECT