Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

lead

View as MarkdownEdit this pageReport an issueSet up docs MCP

順序付けされたフレーム内で、現在の行から offset 行後の行で評価された値を返します。 この関数は leadInFrame に似ていますが、常に ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING フレームを使用します。

構文

lead(x[, offset[, default]])
  OVER ([[PARTITION BY grouping_column] [ORDER BY sorting_column]] | [window_name])
FROM table_name
WINDOW window_name as ([[PARTITION BY grouping_column] [ORDER BY sorting_column])

ウィンドウ関数の構文の詳細については、Window Functions - Syntax を参照してください。

パラメータ

  • x — カラム名。
  • offset — 適用するオフセット。(U)Int*。 (省略可能。デフォルトは 1) 。
  • default — 計算対象の行がウィンドウフレームの境界を超えた場合に返す値。 (省略可能。未指定時はカラム型のデフォルト値) 。

戻り値

  • 順序付けされたフレーム内で、現在の行から offset 行後の行で評価された値。

例

この例では、ノーベル賞受賞者の過去データを参照し、lead 関数を使用して物理学部門の連続する受賞者の一覧を返します。

Querysql
CREATE OR REPLACE VIEW nobel_prize_laureates
AS SELECT *
FROM file('nobel_laureates_data.csv');
Querysql
SELECT
    fullName,
    lead(year, 1, year) OVER (PARTITION BY category ORDER BY year ASC
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS year,
    category,
    motivation
FROM nobel_prize_laureates
WHERE category = 'physics'
ORDER BY year DESC
LIMIT 9
Queryresponse
   ┌─fullName─────────┬─year─┬─category─┬─motivation─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
1. │ Anne L Huillier  │ 2023 │ physics  │ for experimental methods that generate attosecond pulses of light for the study of electron dynamics in matter                     │
2. │ Pierre Agostini  │ 2023 │ physics  │ for experimental methods that generate attosecond pulses of light for the study of electron dynamics in matter                     │
3. │ Ferenc Krausz    │ 2023 │ physics  │ for experimental methods that generate attosecond pulses of light for the study of electron dynamics in matter                     │
4. │ Alain Aspect     │ 2022 │ physics  │ for experiments with entangled photons establishing the violation of Bell inequalities and  pioneering quantum information science │
5. │ Anton Zeilinger  │ 2022 │ physics  │ for experiments with entangled photons establishing the violation of Bell inequalities and  pioneering quantum information science │
6. │ John Clauser     │ 2022 │ physics  │ for experiments with entangled photons establishing the violation of Bell inequalities and  pioneering quantum information science │
7. │ Giorgio Parisi   │ 2021 │ physics  │ for the discovery of the interplay of disorder and fluctuations in physical systems from atomic to planetary scales                │
8. │ Klaus Hasselmann │ 2021 │ physics  │ for the physical modelling of Earths climate quantifying variability and reliably predicting global warming                        │
9. │ Syukuro Manabe   │ 2021 │ physics  │ for the physical modelling of Earths climate quantifying variability and reliably predicting global warming                        │
   └──────────────────┴──────┴──────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Navigation