Repository: kayak/pypika
Pull request #817 (2024-09-12 12:38:23)

Add DISTINCT ON and LIMIT BY support to ClickHouse dialect
This adds [`DISTINCT ON`](https://clickhouse.com/docs/en/sql-reference/statements/select/distinct) and [`LIMIT BY`](https://clickhouse.com/docs/en/sql-reference/statements/select/limit-by) support to the ClickHouse dialect.

I'm not too happy that I had to add the `LIMIT BY` to the `_apply_pagination` method and pass the `**kwargs` around but I don't see any less intrusive way of doing this at the moment.

coveralls: 
[![Coverage Status](https://coveralls.io/builds/69760683/badge)](https://coveralls.io/builds/69760683)

coverage: 98.438% (+0.04%) from 98.398%
when pulling **43cd358afe8fc101560983b297efa8e493cc8ce6 on bikeshedder:improve_clickhouse_dialect**
into **53a77eb9b5bda70fd0780b66a4f68daa14fe3682 on kayak:master**.


Patch:
diff --git a/.github/workflows/ci.yml b/.github/workflows/ci.yml
index cce2084..74a31f2 100644
--- a/.github/workflows/ci.yml
+++ b/.github/workflows/ci.yml
@@ -11,7 +11,7 @@ jobs:
     runs-on: ubuntu-latest
     steps:
       - uses: actions/checkout@v4
-      - uses: actions/setup-python@v4
+      - uses: actions/setup-python@v5
       - uses: pre-commit/action@v3.0.0
 
   test:
@@ -20,12 +20,12 @@ jobs:
     strategy:
       max-parallel: 5
       matrix:
-        python-version: ["3.8", "3.9", "3.10", "3.11"]
+        python-version: ["3.8", "3.9", "3.10", "3.11", "3.12"]
 
     steps:
       - uses: actions/checkout@v4
       - name: Set up Python ${{ matrix.python-version }}
-        uses: actions/setup-python@v4
+        uses: actions/setup-python@v5
         with:
           python-version: ${{ matrix.python-version }}
 
diff --git a/README.rst b/README.rst
index 31bb94f..6794643 100644
--- a/README.rst
+++ b/README.rst
@@ -510,7 +510,7 @@ Example of a correlated subquery in the `SELECT`
 Unions
 """"""
 
-Both ``UNION`` and ``UNION ALL`` are supported. ``UNION DISTINCT`` is synonymous with "UNION`` so |Brand| does not
+Both ``UNION`` and ``UNION ALL`` are supported. ``UNION DISTINCT`` is synonymous with ``UNION`` so |Brand| does not
 provide a separate function for it.  Unions require that queries have the same number of ``SELECT`` clauses so
 trying to cast a unioned query to string will throw a ``SetOperationException`` if the column sizes are mismatched.
 
diff --git a/pypika/__init__.py b/pypika/__init__.py
index a875ae0..66f564f 100644
--- a/pypika/__init__.py
+++ b/pypika/__init__.py
@@ -112,9 +112,63 @@ from pypika.utils import (
     FunctionException,
 )
 
+
 __author__ = "Timothy Heys"
 __email__ = "theys@kayak.com"
 __version__ = "0.48.9"
 
 NULL = NullValue()
 SYSTEM_TIME = SystemTimeValue()
+
+__all__ = (
+    'ClickHouseQuery',
+    'Dialects',
+    'MSSQLQuery',
+    'MySQLQuery',
+    'OracleQuery',
+    'PostgreSQLQuery',
+    'RedshiftQuery',
+    'SQLLiteQuery',
+    'VerticaQuery',
+    'DatePart',
+    'JoinType',
+    'Order',
+    'AliasedQuery',
+    'Query',
+    'Schema',
+    'Table',
+    'Column',
+    'Database',
+    'Tables',
+    'Columns',
+    'Array',
+    'Bracket',
+    'Case',
+    'Criterion',
+    'EmptyCriterion',
+    'Field',
+    'Index',
+    'Interval',
+    'JSON',
+    'Not',
+    'NullValue',
+    'SystemTimeValue',
+    'Parameter',
+    'QmarkParameter',
+    'NumericParameter',
+    'NamedParameter',
+    'FormatParameter',
+    'PyformatParameter',
+    'Rollup',
+    'Tuple',
+    'CustomFunction',
+    'CaseException',
+    'GroupingException',
+    'JoinException',
+    'QueryException',
+    'RollupException',
+    'SetOperationException',
+    'FunctionException',
+    'NULL',
+    'SYSTEM_TIME',
+)
diff --git a/pypika/dialects.py b/pypika/dialects.py
index dab72e0..57955d8 100644
--- a/pypika/dialects.py
+++ b/pypika/dialects.py
@@ -1,7 +1,7 @@
 import itertools
 import warnings
 from copy import copy
-from typing import Any, Optional, Union, Tuple as TypedTuple
+from typing import Any, Optional, Union, Tuple as TypedTuple, List
 
 from pypika.enums import Dialects
 from pypika.queries import (
@@ -384,7 +384,7 @@ class OracleQueryBuilder(FetchNextAndOffsetRowsQueryBuilder):
         kwargs['groupby_alias'] = False
         return super().get_sql(*args, **kwargs)
 
-    def _apply_pagination(self, querystring: str) -> str:
+    def _apply_pagination(self, querystring: str, **kwargs) -> str:
         # Note: Overridden as Oracle specifies offset before the fetch next limit
         if self._offset:
             querystring += self._offset_sql()
@@ -719,7 +719,7 @@ class MSSQLQueryBuilder(FetchNextAndOffsetRowsQueryBuilder):
         self._top_percent: bool = percent
         self._top_with_ties: bool = with_ties
 
-    def _apply_pagination(self, querystring: str) -> str:
+    def _apply_pagination(self, querystring: str, **kwargs) -> str:
         # Note: Overridden as MSSQL specifies offset before the fetch next limit
         if self._limit is not None or self._offset:
             # Offset has to be present if fetch next is specified in a MSSQL query
@@ -794,10 +794,25 @@ class ClickHouseQuery(Query):
 class ClickHouseQueryBuilder(QueryBuilder):
     QUERY_CLS = ClickHouseQuery
 
+    _distinct_on: List[Term]
+    _limit_by: Optional[TypedTuple[int, int, List[Term]]]
+
     def __init__(self, **kwargs) -> None:
         super().__init__(**kwargs)
+        self._final = False
         self._sample = None
         self._sample_offset = None
+        self._distinct_on = []
+        self._limit_by = None
+
+    def __copy__(self) -> "ClickHouseQueryBuilder":
+        newone = super().__copy__()
+        newone._limit_by = copy(self._limit_by)
+        return newone
+
+    @builder
+    def final(self) -> "ClickHouseQueryBuilder":
+        self._final = True
 
     @builder
     def sample(self, sample: int, offset: Optional[int] = None) -> "ClickHouseQueryBuilder":
@@ -816,6 +831,8 @@ class ClickHouseQueryBuilder(QueryBuilder):
         if self._delete_from:
             return " {selectable} DELETE".format(selectable=selectable)
         clauses = [selectable]
+        if self._final is not False:
+            clauses.append("FINAL")
         if self._sample is not None:
             clauses.append(f"SAMPLE {self._sample}")
         if self._sample_offset is not None:
@@ -832,6 +849,55 @@ class ClickHouseQueryBuilder(QueryBuilder):
             )
         )
 
+    @builder
+    def distinct_on(self, *fields: Union[str, Term]) -> "ClickHouseQueryBuilder":
+        for field in fields:
+            if isinstance(field, str):
+                self._distinct_on.append(Field(field))
+            elif isinstance(field, Term):
+                self._distinct_on.append(field)
+
+    def _distinct_sql(self, **kwargs: Any) -> str:
+        if self._distinct_on:
+            return "DISTINCT ON({distinct_on}) ".format(
+                distinct_on=",".join(term.get_sql(with_alias=True, **kwargs) for term in self._distinct_on)
+            )
+        return super()._distinct_sql(**kwargs)
+
+    @builder
+    def limit_by(self, n, *by: Union[str, Term]) -> "ClickHouseQueryBuilder":
+        self._limit_by = (n, 0, [Field(field) if isinstance(field, str) else field for field in by])
+
+    @builder
+    def limit_offset_by(self, n, offset, *by: Union[str, Term]) -> "ClickHouseQueryBuilder":
+        self._limit_by = (n, offset, [Field(field) if isinstance(field, str) else field for field in by])
+
+    def _apply_pagination(self, querystring: str, **kwargs) -> str:
+        # LIMIT BY isn't really a pagination per se but since we need
+        # to add this to the query right before an actual LIMIT clause
+        # this is good enough.
+        if self._limit_by:
+            querystring += self._limit_by_sql(**kwargs)
+        return super()._apply_pagination(querystring, **kwargs)
+
+    def _limit_by_sql(self, **kwargs: Any) -> str:
+        (n, offset, by) = self._limit_by
+        by = ",".join(term.get_sql(with_alias=True, **kwargs) for term in by)
+        if offset != 0:
+            return f" LIMIT {n} OFFSET {offset} BY ({by})"
+        else:
+            return f" LIMIT {n} BY ({by})"
+
+    def replace_table(self, current_table: Optional[Table], new_table: Optional[Table]) -> "ClickHouseQueryBuilder":
+        newone = super().replace_table(current_table, new_table)
+        if self._limit_by:
+            newone._limit_by = (
+                self._limit_by[0],
+                self._limit_by[1],
+                [column.replace_table(current_table, new_table) for column in self._limit_by[2]],
+            )
+        return newone
+
 
 class ClickHouseDropQueryBuilder(DropQueryBuilder):
     QUERY_CLS = ClickHouseQuery
diff --git a/pypika/functions.py b/pypika/functions.py
index c820040..5e693f0 100644
--- a/pypika/functions.py
+++ b/pypika/functions.py
@@ -1,6 +1,11 @@
 """
 Package for SQL functions wrappers
 """
+from __future__ import annotations
+
+from typing import Optional
+
+from pypika import Field
 from pypika.enums import SqlTypes
 from pypika.terms import (
     AggregateFunction,
@@ -10,6 +15,7 @@ from pypika.terms import (
 )
 from pypika.utils import builder
 
+
 __author__ = "Timothy Heys"
 __email__ = "theys@kayak.com"
 
@@ -34,64 +40,64 @@ class DistinctOptionFunction(AggregateFunction):
 
 
 class Count(DistinctOptionFunction):
-    def __init__(self, param, alias=None):
+    def __init__(self, param: str | Field, alias: Optional[str] = None) -> None:
         is_star = isinstance(param, str) and "*" == param
         super(Count, self).__init__("COUNT", Star() if is_star else param, alias=alias)
 
 
 # Arithmetic Functions
 class Sum(DistinctOptionFunction):
-    def __init__(self, term, alias=None):
+    def __init__(self, term: str | Field, alias: Optional[str] = None):
         super(Sum, self).__init__("SUM", term, alias=alias)
 
 
 class Avg(AggregateFunction):
-    def __init__(self, term, alias=None):
+    def __init__(self, term: str | Field, alias: Optional[str] = None):
         super(Avg, self).__init__("AVG", term, alias=alias)
 
 
 class Min(AggregateFunction):
-    def __init__(self, term, alias=None):
+    def __init__(self, term: str | Field, alias: Optional[str] = None):
         super(Min, self).__init__("MIN", term, alias=alias)
 
 
 class Max(AggregateFunction):
-    def __init__(self, term, alias=None):
+    def __init__(self, term: str | Field, alias: Optional[str] = None):
         super(Max, self).__init__("MAX", term, alias=alias)
 
 
 class Std(AggregateFunction):
-    def __init__(self, term, alias=None):
+    def __init__(self, term: str | Field, alias: Optional[str] = None):
         super(Std, self).__init__("STD", term, alias=alias)
 
 
 class StdDev(AggregateFunction):
-    def __init__(self, term, alias=None):
+    def __init__(self, term: str | Field, alias: Optional[str] = None):
         super(StdDev, self).__init__("STDDEV", term, alias=alias)
 
 
 class Abs(AggregateFunction):
-    def __init__(self, term, alias=None):
+    def __init__(self, term: str | Field, alias: Optional[str] = None):
         super(Abs, self).__init__("ABS", term, alias=alias)
 
 
 class First(AggregateFunction):
-    def __init__(self, term, alias=None):
+    def __init__(self, term: str | Field, alias: Optional[str] = None):
         super(First, self).__init__("FIRST", term, alias=alias)
 
 
 class Last(AggregateFunction):
-    def __init__(self, term, alias=None):
+    def __init__(self, term: str | Field, alias: Optional[str] = None):
         super(Last, self).__init__("LAST", term, alias=alias)
 
 
 class Sqrt(Function):
-    def __init__(self, term, alias=None):
+    def __init__(self, term: str | Field, alias: Optional[str] = None):
         super(Sqrt, self).__init__("SQRT", term, alias=alias)
 
 
 class Floor(Function):
-    def __init__(self, term, alias=None):
+    def __init__(self, term: str | Field, alias: Optional[str] = None):
         super(Floor, self).__init__("FLOOR", term, alias=alias)
 
 
@@ -131,17 +137,17 @@ class ToChar(Function):
 
 
 class Signed(Cast):
-    def __init__(self, term, alias=None):
+    def __init__(self, term: str | Field, alias: Optional[str] = None):
         super(Signed, self).__init__(term, SqlTypes.SIGNED, alias=alias)
 
 
 class Unsigned(Cast):
-    def __init__(self, term, alias=None):
+    def __init__(self, term: str | Field, alias: Optional[str] = None):
         super(Unsigned, self).__init__(term, SqlTypes.UNSIGNED, alias=alias)
 
 
 class Date(Function):
-    def __init__(self, term, alias=None):
+    def __init__(self, term: str | Field, alias: Optional[str] = None):
         super(Date, self).__init__("DATE", term, alias=alias)
 
 
@@ -156,7 +162,7 @@ class TimeDiff(Function):
 
 
 class DateAdd(Function):
-    def __init__(self, date_part, interval, term, alias=None):
+    def __init__(self, date_part, interval, term: str, alias: Optional[str] = None):
         date_part = getattr(date_part, "value", date_part)
         super(DateAdd, self).__init__("DATE_ADD", LiteralValue(date_part), interval, term, alias=alias)
 
@@ -167,19 +173,19 @@ class ToDate(Function):
 
 
 class Timestamp(Function):
-    def __init__(self, term, alias=None):
+    def __init__(self, term: str | Field, alias: Optional[str] = None):
         super(Timestamp, self).__init__("TIMESTAMP", term, alias=alias)
 
 
 class TimestampAdd(Function):
-    def __init__(self, date_part, interval, term, alias=None):
+    def __init__(self, date_part, interval, term: str, alias: Optional[str] = None):
         date_part = getattr(date_part, 'value', date_part)
         super(TimestampAdd, self).__init__("TIMESTAMPADD", LiteralValue(date_part), interval, term, alias=alias)
 
 
 # String Functions
 class Ascii(Function):
-    def __init__(self, term, alias=None):
+    def __init__(self, term: str | Field, alias: Optional[str] = None):
         super(Ascii, self).__init__("ASCII", term, alias=alias)
 
 
@@ -189,7 +195,7 @@ class NullIf(Function):
 
 
 class Bin(Function):
-    def __init__(self, term, alias=None):
+    def __init__(self, term: str | Field, alias: Optional[str] = None):
         super(Bin, self).__init__("BIN", term, alias=alias)
 
 
@@ -205,17 +211,17 @@ class Insert(Function):
 
 
 class Length(Function):
-    def __init__(self, term, alias=None):
+    def __init__(self, term: str | Field, alias: Optional[str] = None):
         super(Length, self).__init__("LENGTH", term, alias=alias)
 
 
 class Upper(Function):
-    def __init__(self, term, alias=None):
+    def __init__(self, term: str | Field, alias: Optional[str] = None):
         super(Upper, self).__init__("UPPER", term, alias=alias)
 
 
 class Lower(Function):
-    def __init__(self, term, alias=None):
+    def __init__(self, term: str | Field, alias: Optional[str] = None):
         super(Lower, self).__init__("LOWER", term, alias=alias)
 
 
@@ -225,12 +231,12 @@ class Substring(Function):
 
 
 class Reverse(Function):
-    def __init__(self, term, alias=None):
+    def __init__(self, term: str | Field, alias: Optional[str] = None):
         super(Reverse, self).__init__("REVERSE", term, alias=alias)
 
 
 class Trim(Function):
-    def __init__(self, term, alias=None):
+    def __init__(self, term: str | Field, alias: Optional[str] = None):
         super(Trim, self).__init__("TRIM", term, alias=alias)
 
 
@@ -297,7 +303,7 @@ class Extract(Function):
 
 # Null Functions
 class IsNull(Function):
-    def __init__(self, term, alias=None):
+    def __init__(self, term: str | Field, alias: Optional[str] = None):
         super(IsNull, self).__init__("ISNULL", term, alias=alias)
 
 
@@ -312,5 +318,5 @@ class IfNull(Function):
 
 
 class NVL(Function):
-    def __init__(self, condition, term, alias=None):
+    def __init__(self, condition, term: str, alias: Optional[str] = None):
         super(NVL, self).__init__("NVL", condition, term, alias=alias)
diff --git a/pypika/py.typed b/pypika/py.typed
new file mode 100644
index 0000000..8b13789
--- /dev/null
+++ b/pypika/py.typed
@@ -0,0 +1,1 @@
+
diff --git a/pypika/queries.py b/pypika/queries.py
index c51c6b2..42c7c45 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -206,7 +206,7 @@ class Table(Selectable):
     def __hash__(self) -> int:
         return hash(str(self))
 
-    def select(self, *terms: Sequence[Union[int, float, str, bool, Term, Field]]) -> "QueryBuilder":
+    def select(self, *terms: Union[int, float, str, bool, Term, Field]) -> "QueryBuilder":
         """
         Perform a SELECT operation on the current table
 
@@ -1354,7 +1354,7 @@ class QueryBuilder(Selectable, Term):
         if self._orderbys:
             querystring += self._orderby_sql(**kwargs)
 
-        querystring = self._apply_pagination(querystring)
+        querystring = self._apply_pagination(querystring, **kwargs)
 
         if self._for_update:
             querystring += self._for_update_sql(**kwargs)
@@ -1370,7 +1370,7 @@ class QueryBuilder(Selectable, Term):
 
         return querystring
 
-    def _apply_pagination(self, querystring: str) -> str:
+    def _apply_pagination(self, querystring: str, **kwargs) -> str:
         if self._limit is not None:
             querystring += self._limit_sql()
 
diff --git a/pypika/terms.py b/pypika/terms.py
index ce7aed6..a277e1a 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -3,7 +3,21 @@ import re
 import uuid
 from datetime import date
 from enum import Enum
-from typing import TYPE_CHECKING, Any, Iterable, Iterator, List, Optional, Sequence, Set, Type, TypeVar, Union
+from typing import (
+    TYPE_CHECKING,
+    Any,
+    Callable,
+    Iterable,
+    Iterator,
+    List,
+    Optional,
+    Sequence,
+    Set,
+    Tuple,
+    Type,
+    TypeVar,
+    Union,
+)
 
 from pypika.enums import Arithmetic, Boolean, Comparator, Dialects, Equality, JSONOperators, Matching, Order
 from pypika.utils import (
@@ -288,57 +302,111 @@ class Term(Node):
         raise NotImplementedError()
 
 
+def idx_placeholder_gen(idx: int) -> str:
+    return str(idx + 1)
+
+
+def named_placeholder_gen(idx: int) -> str:
+    return f'param{idx + 1}'
+
+
 class Parameter(Term):
     is_aggregate = None
 
     def __init__(self, placeholder: Union[str, int]) -> None:
         super().__init__()
-        self.placeholder = placeholder
+        self._placeholder = placeholder
+
+    @property
+    def placeholder(self):
+        return self._placeholder
 
     def get_sql(self, **kwargs: Any) -> str:
         return str(self.placeholder)
 
+    def update_parameters(self, param_key: Any, param_value: Any, **kwargs):
+        pass
 
-class QmarkParameter(Parameter):
-    """Question mark style, e.g. ...WHERE name=?"""
+    def get_param_key(self, placeholder: Any, **kwargs):
+        return placeholder
 
-    def __init__(self) -> None:
-        pass
 
-    def get_sql(self, **kwargs: Any) -> str:
-        return "?"
+class ListParameter(Parameter):
+    def __init__(self, placeholder: Union[str, int, Callable[[int], str]] = idx_placeholder_gen) -> None:
+        super().__init__(placeholder=placeholder)
+        self._parameters = list()
 
+    @property
+    def placeholder(self) -> str:
+        if callable(self._placeholder):
+            return self._placeholder(len(self._parameters))
 
-class NumericParameter(Parameter):
-    """Numeric, positional style, e.g. ...WHERE name=:1"""
+        return str(self._placeholder)
 
-    def get_sql(self, **kwargs: Any) -> str:
-        return ":{placeholder}".format(placeholder=self.placeholder)
+    def get_parameters(self, **kwargs):
+        return self._parameters
 
+    def update_parameters(self, value: Any, **kwargs):
+        self._parameters.append(value)
 
-class NamedParameter(Parameter):
-    """Named style, e.g. ...WHERE name=:name"""
+
+class DictParameter(Parameter):
+    def __init__(self, placeholder: Union[str, int, Callable[[int], str]] = named_placeholder_gen) -> None:
+        super().__init__(placeholder=placeholder)
+        self._parameters = dict()
+
+    @property
+    def placeholder(self) -> str:
+        if callable(self._placeholder):
+            return self._placeholder(len(self._parameters))
+
+        return str(self._placeholder)
+
+    def get_parameters(self, **kwargs):
+        return self._parameters
+
+    def get_param_key(self, placeholder: Any, **kwargs):
+        return placeholder[1:]
+
+    def update_parameters(self, param_key: Any, value: Any, **kwargs):
+        self._parameters[param_key] = value
+
+
+class QmarkParameter(ListParameter):
+    def get_sql(self, **kwargs):
+        return '?'
+
+
+class NumericParameter(ListParameter):
+    """Numeric, positional style, e.g. ...WHERE name=:1"""
 
     def get_sql(self, **kwargs: Any) -> str:
         return ":{placeholder}".format(placeholder=self.placeholder)
 
 
-class FormatParameter(Parameter):
+class FormatParameter(ListParameter):
     """ANSI C printf format codes, e.g. ...WHERE name=%s"""
 
-    def __init__(self) -> None:
-        pass
-
     def get_sql(self, **kwargs: Any) -> str:
         return "%s"
 
 
-class PyformatParameter(Parameter):
+class NamedParameter(DictParameter):
+    """Named style, e.g. ...WHERE name=:name"""
+
+    def get_sql(self, **kwargs: Any) -> str:
+        return ":{placeholder}".format(placeholder=self.placeholder)
+
+
+class PyformatParameter(DictParameter):
     """Python extended format codes, e.g. ...WHERE name=%(name)s"""
 
     def get_sql(self, **kwargs: Any) -> str:
         return "%({placeholder})s".format(placeholder=self.placeholder)
 
+    def get_param_key(self, placeholder: Any, **kwargs):
+        return placeholder[2:-2]
+
 
 class Negative(Term):
     def __init__(self, term: Term) -> None:
@@ -385,9 +453,44 @@ class ValueWrapper(Term):
             return "null"
         return str(value)
 
-    def get_sql(self, quote_char: Optional[str] = None, secondary_quote_char: str = "'", **kwargs: Any) -> str:
-        sql = self.get_value_sql(quote_char=quote_char, secondary_quote_char=secondary_quote_char, **kwargs)
-        return format_alias_sql(sql, self.alias, quote_char=quote_char, **kwargs)
+    def _get_param_data(self, parameter: Parameter, **kwargs) -> Tuple[str, str]:
+        param_sql = parameter.get_sql(**kwargs)
+        param_key = parameter.get_param_key(placeholder=param_sql)
+
+        return param_sql, param_key
+
+    def get_sql(
+        self,
+        quote_char: Optional[str] = None,
+        secondary_quote_char: str = "'",
+        parameter: Parameter = None,
+        **kwargs: Any,
+    ) -> str:
+        if parameter is None:
+            sql = self.get_value_sql(quote_char=quote_char, secondary_quote_char=secondary_quote_char, **kwargs)
+            return format_alias_sql(sql, self.alias, quote_char=quote_char, **kwargs)
+
+        # Don't stringify numbers when using a parameter
+        if isinstance(self.value, (int, float)):
+            value_sql = self.value
+        else:
+            value_sql = self.get_value_sql(quote_char=quote_char, **kwargs)
+        param_sql, param_key = self._get_param_data(parameter, **kwargs)
+        parameter.update_parameters(param_key=param_key, value=value_sql, **kwargs)
+
+        return format_alias_sql(param_sql, self.alias, quote_char=quote_char, **kwargs)
+
+
+class ParameterValueWrapper(ValueWrapper):
+    def __init__(self, parameter: Parameter, value: Any, alias: Optional[str] = None) -> None:
+        super().__init__(value, alias)
+        self._parameter = parameter
+
+    def _get_param_data(self, parameter: Parameter, **kwargs) -> Tuple[str, str]:
+        param_sql = self._parameter.get_sql(**kwargs)
+        param_key = self._parameter.get_param_key(placeholder=param_sql)
+
+        return param_sql, param_key
 
 
 class JSON(Term):
@@ -551,6 +654,7 @@ class Field(Criterion, JSON):
         if isinstance(table, str):
             # avoid circular import at load time
             from pypika.queries import Table
+
             table = Table(table)
         self.table = table
 
diff --git a/setup.py b/setup.py
index fb376d4..41cce7d 100644
--- a/setup.py
+++ b/setup.py
@@ -48,6 +48,7 @@ setup(
       packages=["pypika", "pypika.clickhouse"],
 
       # Include additional files into the package
+      package_data={"pypika": ["py.typed"]},
       include_package_data=True,
 
       install_requires=[],


Test patch:
diff --git a/pypika/tests/dialects/test_clickhouse.py b/pypika/tests/dialects/test_clickhouse.py
index 8da6270..f319c46 100644
--- a/pypika/tests/dialects/test_clickhouse.py
+++ b/pypika/tests/dialects/test_clickhouse.py
@@ -23,6 +23,11 @@ class ClickHouseQueryTests(TestCase):
         query = ClickHouseQuery.from_(t).select(t.foo).sample(10, 5)
         self.assertEqual(str(query), 'SELECT "foo" FROM "abc" SAMPLE 10 OFFSET 5')
 
+    def test_use_FINAL_keyword(self):
+        t = Table('abc')
+        query = ClickHouseQuery.from_(t).select(t.foo).final()
+        self.assertEqual(str(query), 'SELECT "foo" FROM "abc" FINAL')
+
 
 class ClickHouseDeleteTests(TestCase):
     table_abc = Table("abc")
@@ -94,3 +99,48 @@ class ClickHouseDropQuery(TestCase):
         self.assertEqual('DROP QUOTA "myquota"', str(q1))
         self.assertEqual('DROP USER "myuser"', str(q2))
         self.assertEqual('DROP VIEW "myview"', str(q3))
+
+
+class DistinctOnTests(TestCase):
+    table_abc = Table("abc")
+
+    def test_distinct_on(self):
+        q = ClickHouseQuery.from_(self.table_abc).distinct_on("lname", self.table_abc.fname).select("lname", "id")
+
+        self.assertEqual('''SELECT DISTINCT ON("lname","fname") "lname","id" FROM "abc"''', str(q))
+
+
+class LimitByTests(TestCase):
+    table_abc = Table("abc")
+
+    def test_limit_by(self):
+        q = ClickHouseQuery.from_(self.table_abc).limit_by(1, "a", self.table_abc.b).select("a", "b", "c")
+
+        self.assertEqual('''SELECT "a","b","c" FROM "abc" LIMIT 1 BY ("a","b")''', str(q))
+
+    def test_limit_offset_by(self):
+        q = ClickHouseQuery.from_(self.table_abc).limit_offset_by(1, 2, "a", self.table_abc.b).select("a", "b", "c")
+
+        self.assertEqual('''SELECT "a","b","c" FROM "abc" LIMIT 1 OFFSET 2 BY ("a","b")''', str(q))
+
+    def test_limit_offset0_by(self):
+        q = ClickHouseQuery.from_(self.table_abc).limit_offset_by(1, 0, "a", self.table_abc.b).select("a", "b", "c")
+
+        self.assertEqual('''SELECT "a","b","c" FROM "abc" LIMIT 1 BY ("a","b")''', str(q))
+
+    def test_rename_table(self):
+        table_join = Table("join")
+
+        q = (
+            ClickHouseQuery.from_(self.table_abc)
+            .join(table_join)
+            .using("a")
+            .limit_by(1, self.table_abc.a, table_join.a)
+            .select(self.table_abc.b, table_join.b)
+        )
+        q = q.replace_table(self.table_abc, Table("xyz"))
+
+        self.assertEqual(
+            '''SELECT "xyz"."b","join"."b" FROM "xyz" JOIN "join" USING ("a") LIMIT 1 BY ("xyz"."a","join"."a")''',
+            str(q),
+        )
diff --git a/pypika/tests/test_parameter.py b/pypika/tests/test_parameter.py
index e19666a..c11e9af 100644
--- a/pypika/tests/test_parameter.py
+++ b/pypika/tests/test_parameter.py
@@ -1,4 +1,5 @@
 import unittest
+from datetime import date
 
 from pypika import (
     FormatParameter,
@@ -10,6 +11,7 @@ from pypika import (
     Query,
     Tables,
 )
+from pypika.terms import ListParameter, ParameterValueWrapper
 
 
 class ParametrizedTests(unittest.TestCase):
@@ -92,3 +94,113 @@ class ParametrizedTests(unittest.TestCase):
 
     def test_pyformat_parameter(self):
         self.assertEqual('%(buz)s', PyformatParameter('buz').get_sql())
+
+
+class ParametrizedTestsWithValues(unittest.TestCase):
+    table_abc, table_efg = Tables("abc", "efg")
+
+    def test_param_insert(self):
+        q = Query.into(self.table_abc).columns("a", "b", "c").insert(1, 2.2, 'foo')
+
+        parameter = QmarkParameter()
+        sql = q.get_sql(parameter=parameter)
+        self.assertEqual('INSERT INTO "abc" ("a","b","c") VALUES (?,?,?)', sql)
+        self.assertEqual([1, 2.2, 'foo'], parameter.get_parameters())
+
+    def test_param_select_join(self):
+        q = (
+            Query.from_(self.table_abc)
+            .select("*")
+            .where(self.table_abc.category == 'foobar')
+            .join(self.table_efg)
+            .on(self.table_abc.id == self.table_efg.abc_id)
+            .where(self.table_efg.date >= date(2024, 2, 22))
+            .limit(10)
+        )
+
+        parameter = FormatParameter()
+        sql = q.get_sql(parameter=parameter)
+        self.assertEqual(
+            'SELECT * FROM "abc" JOIN "efg" ON "abc"."id"="efg"."abc_id" WHERE "abc"."category"=%s AND "efg"."date">=%s LIMIT 10',
+            sql,
+        )
+        self.assertEqual(['foobar', '2024-02-22'], parameter.get_parameters())
+
+    def test_param_select_subquery(self):
+        q = (
+            Query.from_(self.table_abc)
+            .select("*")
+            .where(self.table_abc.category == 'foobar')
+            .where(
+                self.table_abc.id.isin(
+                    Query.from_(self.table_efg)
+                    .select(self.table_efg.abc_id)
+                    .where(self.table_efg.date >= date(2024, 2, 22))
+                )
+            )
+            .limit(10)
+        )
+
+        parameter = ListParameter(placeholder=lambda idx: f'&{idx+1}')
+        sql = q.get_sql(parameter=parameter)
+        self.assertEqual(
+            'SELECT * FROM "abc" WHERE "category"=&1 AND "id" IN (SELECT "abc_id" FROM "efg" WHERE "date">=&2) LIMIT 10',
+            sql,
+        )
+        self.assertEqual(['foobar', '2024-02-22'], parameter.get_parameters())
+
+    def test_join(self):
+        subquery = (
+            Query.from_(self.table_efg)
+            .select(self.table_efg.fiz, self.table_efg.buz)
+            .where(self.table_efg.buz == 'buz')
+        )
+
+        q = (
+            Query.from_(self.table_abc)
+            .join(subquery)
+            .on(self.table_abc.bar == subquery.buz)
+            .select(self.table_abc.foo, subquery.fiz)
+            .where(self.table_abc.bar == 'bar')
+        )
+
+        parameter = NamedParameter()
+        sql = q.get_sql(parameter=parameter)
+        self.assertEqual(
+            'SELECT "abc"."foo","sq0"."fiz" FROM "abc" JOIN (SELECT "fiz","buz" FROM "efg" WHERE "buz"=:param1)'
+            ' "sq0" ON "abc"."bar"="sq0"."buz" WHERE "abc"."bar"=:param2',
+            sql,
+        )
+        self.assertEqual({'param1': 'buz', 'param2': 'bar'}, parameter.get_parameters())
+
+    def test_join_with_parameter_value_wrapper(self):
+        subquery = (
+            Query.from_(self.table_efg)
+            .select(self.table_efg.fiz, self.table_efg.buz)
+            .where(self.table_efg.buz == ParameterValueWrapper(Parameter(':buz'), 'buz'))
+        )
+
+        q = (
+            Query.from_(self.table_abc)
+            .join(subquery)
+            .on(self.table_abc.bar == subquery.buz)
+            .select(self.table_abc.foo, subquery.fiz)
+            .where(self.table_abc.bar == ParameterValueWrapper(NamedParameter('bar'), 'bar'))
+        )
+
+        parameter = NamedParameter()
+        sql = q.get_sql(parameter=parameter)
+        self.assertEqual(
+            'SELECT "abc"."foo","sq0"."fiz" FROM "abc" JOIN (SELECT "fiz","buz" FROM "efg" WHERE "buz"=:buz)'
+            ' "sq0" ON "abc"."bar"="sq0"."buz" WHERE "abc"."bar"=:bar',
+            sql,
+        )
+        self.assertEqual({':buz': 'buz', 'bar': 'bar'}, parameter.get_parameters())
+
+    def test_pyformat_parameter(self):
+        q = Query.into(self.table_abc).columns("a", "b", "c").insert(1, 2.2, 'foo')
+
+        parameter = PyformatParameter()
+        sql = q.get_sql(parameter=parameter)
+        self.assertEqual('INSERT INTO "abc" ("a","b","c") VALUES (%(param1)s,%(param2)s,%(param3)s)', sql)
+        self.assertEqual({"param1": 1, "param2": 2.2, "param3": "foo"}, parameter.get_parameters())
diff --git a/pypika/tests/test_terms.py b/pypika/tests/test_terms.py
index 607c4c0..4c7590d 100644
--- a/pypika/tests/test_terms.py
+++ b/pypika/tests/test_terms.py
@@ -20,7 +20,7 @@ class FieldInitTests(TestCase):
         test_table_name = "test_table"
         field = Field(name="name", table=test_table_name)
         self.assertEqual(field.table, Table(name=test_table_name))
-    
+
 
 class FieldHashingTests(TestCase):
     def test_tabled_eq_fields_equally_hashed(self):