Mercurial > urweb

comparison doc/manual.tex @ 1777:59b07fdae1ff

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Partitioning and ordering for window functions
author Adam Chlipala <adam@chlipala.net>
date Sat, 02 Jun 2012 16:47:09 -0400
parents 6bc2a8cb3a67
children 818d4097e2ed
comparison
equal deleted inserted replaced
1776:8f28c3295148 1777:59b07fdae1ff
1803 1803
1804 $$\begin{array}{l} 1804 $$\begin{array}{l}
1805 \mt{class} \; \mt{sql\_summable} \\ 1805 \mt{class} \; \mt{sql\_summable} \\
1806 \mt{val} \; \mt{sql\_summable\_int} : \mt{sql\_summable} \; \mt{int} \\ 1806 \mt{val} \; \mt{sql\_summable\_int} : \mt{sql\_summable} \; \mt{int} \\
1807 \mt{val} \; \mt{sql\_summable\_float} : \mt{sql\_summable} \; \mt{float} \\ 1807 \mt{val} \; \mt{sql\_summable\_float} : \mt{sql\_summable} \; \mt{float} \\
1808 \mt{val} \; \mt{sql\_avg} : \mt{t} ::: \mt{Type} \to \mt{nt} ::: \mt{Type} \to \mt{sql\_summable} \; \mt{t} \to \mt{nullify} \; \mt{t} \; \mt{nt} \to \mt{sql\_aggregate} \; \mt{t} \; \mt{nt} \\ 1808 \mt{val} \; \mt{sql\_avg} : \mt{t} ::: \mt{Type} \to \mt{sql\_summable} \; \mt{t} \to \mt{sql\_aggregate} \; \mt{t} \; (\mt{option} \; \mt{float}) \\
1809 \mt{val} \; \mt{sql\_sum} : \mt{t} ::: \mt{Type} \to \mt{nt} ::: \mt{Type} \to \mt{sql\_summable} \; \mt{t} \to \mt{nullify} \; \mt{t} \; \mt{nt} \to \mt{sql\_aggregate} \; \mt{t} \; \mt{nt} 1809 \mt{val} \; \mt{sql\_sum} : \mt{t} ::: \mt{Type} \to \mt{nt} ::: \mt{Type} \to \mt{sql\_summable} \; \mt{t} \to \mt{nullify} \; \mt{t} \; \mt{nt} \to \mt{sql\_aggregate} \; \mt{t} \; \mt{nt}
1810 \end{array}$$ 1810 \end{array}$$
1811 1811
1812 $$\begin{array}{l} 1812 $$\begin{array}{l}
1813 \mt{class} \; \mt{sql\_maxable} \\ 1813 \mt{class} \; \mt{sql\_maxable} \\
1817 \mt{val} \; \mt{sql\_maxable\_time} : \mt{sql\_maxable} \; \mt{time} \\ 1817 \mt{val} \; \mt{sql\_maxable\_time} : \mt{sql\_maxable} \; \mt{time} \\
1818 \mt{val} \; \mt{sql\_max} : \mt{t} ::: \mt{Type} \to \mt{nt} ::: \mt{Type} \to \mt{sql\_maxable} \; \mt{t} \to \mt{nullify} \; \mt{t} \; \mt{nt} \to \mt{sql\_aggregate} \; \mt{t} \; \mt{nt} \\ 1818 \mt{val} \; \mt{sql\_max} : \mt{t} ::: \mt{Type} \to \mt{nt} ::: \mt{Type} \to \mt{sql\_maxable} \; \mt{t} \to \mt{nullify} \; \mt{t} \; \mt{nt} \to \mt{sql\_aggregate} \; \mt{t} \; \mt{nt} \\
1819 \mt{val} \; \mt{sql\_min} : \mt{t} ::: \mt{Type} \to \mt{nt} ::: \mt{Type} \to \mt{sql\_maxable} \; \mt{t} \to \mt{nullify} \; \mt{t} \; \mt{nt} \to \mt{sql\_aggregate} \; \mt{t} \; \mt{nt} 1819 \mt{val} \; \mt{sql\_min} : \mt{t} ::: \mt{Type} \to \mt{nt} ::: \mt{Type} \to \mt{sql\_maxable} \; \mt{t} \to \mt{nullify} \; \mt{t} \; \mt{nt} \to \mt{sql\_aggregate} \; \mt{t} \; \mt{nt}
1820 \end{array}$$ 1820 \end{array}$$
1821 1821
1822 There is a fancier class of aggregates called \emph{window functions}, defined in the SQL standard but currently only supported by Postgres, among the DBMSes that Ur/Web supports. Here are the type family and associated combinator for creating a window function expression:
1823
1824 $$\begin{array}{l}
1825 \mt{con} \; \mt{sql\_window} :: \{\{\mt{Type}\}\} \to \{\{\mt{Type}\}\} \to \{\mt{Type}\} \to \mt{Type} \to \mt{Type} \\
1826 \mt{val} \; \mt{sql\_window} : \mt{tables} ::: \{\{\mt{Type}\}\} \to \mt{agg} ::: \{\{\mt{Type}\}\} \to \mt{exps} ::: \{\mt{Type}\} \\
1827 \hspace{.1in} \to \mt{t} ::: \mt{Type} \\
1828 \hspace{.1in} \to \mt{sql\_window} \; \mt{tables} \; \mt{agg} \; \mt{exps} \; \mt{t} \\
1829 \hspace{.1in} \to \mt{sql\_partition} \; \mt{tables} \; \mt{agg} \; \mt{exps} \\
1830 \hspace{.1in} \to \mt{sql\_order\_by} \; \mt{tables} \; \mt{exps} \\
1831 \hspace{.1in} \to \mt{sql\_exp} \; \mt{tables} \; \mt{agg} \; \mt{exps} \; \mt{allow\_window} \; \mt{t}
1832 \end{array}$$
1833
1834 The function argument for an SQL \cd{PARTITION BY} clause uses the following type family and combinators:
1835 $$\begin{array}{l}
1836 \mt{con} \; \mt{sql\_partition} :: \{\{\mt{Type}\}\} \to \{\{\mt{Type}\}\} \to \{\mt{Type}\} \to \mt{Type} \\
1837 \mt{val} \; \mt{sql\_no\_partition} : \mt{tables} ::: \{\{\mt{Type}\}\} \to \mt{agg} ::: \{\{\mt{Type}\}\} \to \mt{exps} ::: \{\mt{Type}\} \\
1838 \hspace{.1in} \to \mt{sql\_partition} \; \mt{tables} \; \mt{agg} \; \mt{exps} \\
1839 \mt{val} \; \mt{sql\_partition} : \mt{tables} ::: \{\{\mt{Type}\}\} \to \mt{agg} ::: \{\{\mt{Type}\}\} \to \mt{exps} ::: \{\mt{Type}\} \to \mt{t} ::: \mt{Type} \\
1840 \hspace{.1in} \to \mt{sql\_exp} \; \mt{tables} \; \mt{agg} \; \mt{exps} \; \mt{disallow\_window} \; \mt{t} \\
1841 \hspace{.1in} \to \mt{sql\_partition} \; \mt{tables} \; \mt{agg} \; \mt{exps}
1842 \end{array}$$
1843
1822 Any SQL query that returns single columns may be turned into a subquery expression. 1844 Any SQL query that returns single columns may be turned into a subquery expression.
1823 1845
1824 $$\begin{array}{l} 1846 $$\begin{array}{l}
1825 \mt{val} \; \mt{sql\_subquery} : \mt{tables} ::: \{\{\mt{Type}\}\} \to \mt{agg} ::: \{\{\mt{Type}\}\} \to \mt{exps} ::: \{\mt{Type}\} \to \mt{nm} ::: \mt{Name} \\ 1847 \mt{val} \; \mt{sql\_subquery} : \mt{tables} ::: \{\{\mt{Type}\}\} \to \mt{agg} ::: \{\{\mt{Type}\}\} \to \mt{exps} ::: \{\mt{Type}\} \to \mt{nm} ::: \mt{Name} \\
1826 \hspace{.1in} \to \mt{aw} ::: \mt{Type} \to \mt{t} ::: \mt{Type} \to \mt{nt} ::: \mt{Type} \\ 1848 \hspace{.1in} \to \mt{aw} ::: \{\mt{Unit}\} \to \mt{t} ::: \mt{Type} \to \mt{nt} ::: \mt{Type} \\
1827 \hspace{.1in} \to \mt{nullify} \; \mt{t} \; \mt{nt} \to \mt{sql\_query} \; \mt{tables} \; \mt{agg} \; [\mt{nm} = \mt{t}] \to \mt{sql\_exp} \; \mt{tables} \; \mt{agg} \; \mt{exps} \; \mt{aw} \; \mt{nt} 1849 \hspace{.1in} \to \mt{nullify} \; \mt{t} \; \mt{nt} \to \mt{sql\_query} \; \mt{tables} \; \mt{agg} \; [\mt{nm} = \mt{t}] \to \mt{sql\_exp} \; \mt{tables} \; \mt{agg} \; \mt{exps} \; \mt{aw} \; \mt{nt}
1828 \end{array}$$ 1850 \end{array}$$
1829 1851
1830 There is also an \cd{IF..THEN..ELSE..} construct that is compiled into standard SQL \cd{CASE} expressions. 1852 There is also an \cd{IF..THEN..ELSE..} construct that is compiled into standard SQL \cd{CASE} expressions.
1831 $$\begin{array}{l} 1853 $$\begin{array}{l}
2192 \textrm{Joins} & J &::=& [\mt{INNER}] \\ 2214 \textrm{Joins} & J &::=& [\mt{INNER}] \\
2193 &&& \mid [\mt{LEFT} \mid \mt{RIGHT} \mid \mt{FULL}] \; [\mt{OUTER}] \\ 2215 &&& \mid [\mt{LEFT} \mid \mt{RIGHT} \mid \mt{FULL}] \; [\mt{OUTER}] \\
2194 \textrm{SQL expressions} & E &::=& t.f & \textrm{column references} \\ 2216 \textrm{SQL expressions} & E &::=& t.f & \textrm{column references} \\
2195 &&& X & \textrm{named expression references} \\ 2217 &&& X & \textrm{named expression references} \\
2196 &&& \{[e]\} & \textrm{injected native Ur expressions} \\ 2218 &&& \{[e]\} & \textrm{injected native Ur expressions} \\
2197 &&& \{e\} & \textrm{computed expressions, probably using $\mt{sql\_exp}$ directly} \\ 2219 &&& \{e\} & \textrm{computed expressions, probably using} \\
2220 &&&& \hspace{.1in} \textrm{$\mt{sql\_exp}$ directly} \\
2198 &&& \mt{TRUE} \mid \mt{FALSE} & \textrm{boolean constants} \\ 2221 &&& \mt{TRUE} \mid \mt{FALSE} & \textrm{boolean constants} \\
2199 &&& \ell & \textrm{primitive type literals} \\ 2222 &&& \ell & \textrm{primitive type literals} \\
2200 &&& \mt{NULL} & \textrm{null value (injection of $\mt{None}$)} \\ 2223 &&& \mt{NULL} & \textrm{null value (injection of $\mt{None}$)} \\
2201 &&& E \; \mt{IS} \; \mt{NULL} & \textrm{nullness test} \\ 2224 &&& E \; \mt{IS} \; \mt{NULL} & \textrm{nullness test} \\
2202 &&& \mt{COALESCE}(E, E) & \textrm{take first non-null value} \\ 2225 &&& \mt{COALESCE}(E, E) & \textrm{take first non-null value} \\
2203 &&& n & \textrm{nullary operators} \\ 2226 &&& n & \textrm{nullary operators} \\
2204 &&& u \; E & \textrm{unary operators} \\ 2227 &&& u \; E & \textrm{unary operators} \\
2205 &&& E \; b \; E & \textrm{binary operators} \\ 2228 &&& E \; b \; E & \textrm{binary operators} \\
2206 &&& \mt{COUNT}(\ast) & \textrm{count number of rows} \\ 2229 &&& \mt{COUNT}(\ast) \; [w] & \textrm{count number of rows} \\
2207 &&& a(E) & \textrm{other aggregate function} \\ 2230 &&& \mt{RANK}() \; [w] & \textrm{rank in sequence (Postgres only)} \\
2231 &&& a(E) \; [w] & \textrm{other aggregate function} \\
2208 &&& \mt{IF} \; E \; \mt{THEN} \; E \; \mt{ELSE} \; E & \textrm{conditional} \\ 2232 &&& \mt{IF} \; E \; \mt{THEN} \; E \; \mt{ELSE} \; E & \textrm{conditional} \\
2209 &&& (Q) & \textrm{subquery (must return a single expression column)} \\ 2233 &&& (Q) & \textrm{subquery (must return a single} \\
2234 &&&& \hspace{.1in} \textrm{expression column)} \\
2210 &&& (E) & \textrm{explicit precedence} \\ 2235 &&& (E) & \textrm{explicit precedence} \\
2211 \textrm{Nullary operators} & n &::=& \mt{CURRENT\_TIMESTAMP} \\ 2236 \textrm{Nullary operators} & n &::=& \mt{CURRENT\_TIMESTAMP} \\
2212 \textrm{Unary operators} & u &::=& \mt{NOT} \\ 2237 \textrm{Unary operators} & u &::=& \mt{NOT} \\
2213 \textrm{Binary operators} & b &::=& \mt{AND} \mid \mt{OR} \mid = \mid \neq \mid < \mid \leq \mid > \mid \geq \\ 2238 \textrm{Binary operators} & b &::=& \mt{AND} \mid \mt{OR} \mid = \mid \neq \mid < \mid \leq \mid > \mid \geq \\
2214 \textrm{Aggregate functions} & a &::=& \mt{COUNT} \mid \mt{AVG} \mid \mt{SUM} \mid \mt{MIN} \mid \mt{MAX} \\ 2239 \textrm{Aggregate functions} & a &::=& \mt{COUNT} \mid \mt{AVG} \mid \mt{SUM} \mid \mt{MIN} \mid \mt{MAX} \\
2215 \textrm{Directions} & o &::=& \mt{ASC} \mid \mt{DESC} \mid \{e\} \\ 2240 \textrm{Directions} & o &::=& \mt{ASC} \mid \mt{DESC} \mid \{e\} \\
2216 \textrm{SQL integer} & N &::=& n \mid \{e\} \\ 2241 \textrm{SQL integer} & N &::=& n \mid \{e\} \\
2242 \textrm{Window} & w &::=& \mt{OVER} \; ([\mt{PARTITION} \; \mt{BY} \; E] \; [\mt{ORDER} \; \mt{BY} \; O]) & \textrm{(Postgres only)}
2217 \end{array}$$ 2243 \end{array}$$
2218 2244
2219 Additionally, an SQL expression may be inserted into normal Ur code with the syntax $(\mt{SQL} \; E)$ or $(\mt{WHERE} \; E)$. Similar shorthands exist for other nonterminals, with the prefix $\mt{FROM}$ for $\mt{FROM}$ items and $\mt{SELECT1}$ for pre-queries. 2245 Additionally, an SQL expression may be inserted into normal Ur code with the syntax $(\mt{SQL} \; E)$ or $(\mt{WHERE} \; E)$. Similar shorthands exist for other nonterminals, with the prefix $\mt{FROM}$ for $\mt{FROM}$ items and $\mt{SELECT1}$ for pre-queries.
2220 2246
2221 Unnamed expression columns in $\mt{SELECT}$ clauses are assigned consecutive natural numbers, starting with 1. Any expression in a $p$ position that is enclosed in parentheses is treated as an expression column, rather than a column pulled directly out of a table, even if it is only a field projection. (This distinction affects the record type used to describe query results.) 2247 Unnamed expression columns in $\mt{SELECT}$ clauses are assigned consecutive natural numbers, starting with 1. Any expression in a $p$ position that is enclosed in parentheses is treated as an expression column, rather than a column pulled directly out of a table, even if it is only a field projection. (This distinction affects the record type used to describe query results.)