window_api.ex (7307B)
1 defmodule Ecto.Query.WindowAPI do 2 @moduledoc """ 3 Lists all windows functions. 4 5 Windows functions must always be used as the first argument 6 of `over/2` where the second argument is the name of a window: 7 8 from e in Employee, 9 select: {e.depname, e.empno, e.salary, over(avg(e.salary), :department)}, 10 windows: [department: [partition_by: e.depname]] 11 12 In the example above, we get the average salary per department. 13 `:department` is the window name, partitioned by `e.depname` 14 and `avg/1` is the window function. 15 16 However, note that defining a window is not necessary, as the 17 window definition can be given as the second argument to `over`: 18 19 from e in Employee, 20 select: {e.depname, e.empno, e.salary, over(avg(e.salary), partition_by: e.depname)} 21 22 Both queries are equivalent. However, if you are using the same 23 partitioning over and over again, defining a window will reduce 24 the query size. See `Ecto.Query.windows/3` for all possible window 25 expressions, such as `:partition_by` and `:order_by`. 26 """ 27 28 @dialyzer :no_return 29 30 @doc """ 31 Counts the entries in the table. 32 33 from p in Post, select: count() 34 """ 35 def count, do: doc! [] 36 37 @doc """ 38 Counts the given entry. 39 40 from p in Post, select: count(p.id) 41 """ 42 def count(value), do: doc! [value] 43 44 @doc """ 45 Calculates the average for the given entry. 46 47 from p in Payment, select: avg(p.value) 48 """ 49 def avg(value), do: doc! [value] 50 51 @doc """ 52 Calculates the sum for the given entry. 53 54 from p in Payment, select: sum(p.value) 55 """ 56 def sum(value), do: doc! [value] 57 58 @doc """ 59 Calculates the minimum for the given entry. 60 61 from p in Payment, select: min(p.value) 62 """ 63 def min(value), do: doc! [value] 64 65 @doc """ 66 Calculates the maximum for the given entry. 67 68 from p in Payment, select: max(p.value) 69 """ 70 def max(value), do: doc! [value] 71 72 @doc """ 73 Defines a value based on the function and the window. See moduledoc for more information. 74 75 from e in Employee, select: over(avg(e.salary), partition_by: e.depname) 76 """ 77 def over(window_function, window_name), do: doc! [window_function, window_name] 78 79 @doc """ 80 Returns number of the current row within its partition, counting from 1. 81 82 from p in Post, 83 select: row_number() |> over(partition_by: p.category_id, order_by: p.date) 84 85 Note that this function must be invoked using window function syntax. 86 """ 87 def row_number(), do: doc! [] 88 89 @doc """ 90 Returns rank of the current row with gaps; same as `row_number/0` of its first peer. 91 92 from p in Post, 93 select: rank() |> over(partition_by: p.category_id, order_by: p.date) 94 95 Note that this function must be invoked using window function syntax. 96 """ 97 def rank(), do: doc! [] 98 99 @doc """ 100 Returns rank of the current row without gaps; this function counts peer groups. 101 102 from p in Post, 103 select: dense_rank() |> over(partition_by: p.category_id, order_by: p.date) 104 105 Note that this function must be invoked using window function syntax. 106 """ 107 def dense_rank(), do: doc! [] 108 109 @doc """ 110 Returns relative rank of the current row: (rank - 1) / (total rows - 1). 111 112 from p in Post, 113 select: percent_rank() |> over(partition_by: p.category_id, order_by: p.date) 114 115 Note that this function must be invoked using window function syntax. 116 """ 117 def percent_rank(), do: doc! [] 118 119 @doc """ 120 Returns relative rank of the current row: 121 (number of rows preceding or peer with current row) / (total rows). 122 123 from p in Post, 124 select: cume_dist() |> over(partition_by: p.category_id, order_by: p.date) 125 126 Note that this function must be invoked using window function syntax. 127 """ 128 def cume_dist(), do: doc! [] 129 130 @doc """ 131 Returns integer ranging from 1 to the argument value, dividing the partition as equally as possible. 132 133 from p in Post, 134 select: ntile(10) |> over(partition_by: p.category_id, order_by: p.date) 135 136 Note that this function must be invoked using window function syntax. 137 """ 138 def ntile(num_buckets), do: doc! [num_buckets] 139 140 @doc """ 141 Returns value evaluated at the row that is the first row of the window frame. 142 143 from p in Post, 144 select: first_value(p.id) |> over(partition_by: p.category_id, order_by: p.date) 145 146 Note that this function must be invoked using window function syntax. 147 """ 148 def first_value(value), do: doc! [value] 149 150 @doc """ 151 Returns value evaluated at the row that is the last row of the window frame. 152 153 from p in Post, 154 select: last_value(p.id) |> over(partition_by: p.category_id, order_by: p.date) 155 156 Note that this function must be invoked using window function syntax. 157 """ 158 def last_value(value), do: doc! [value] 159 160 161 @doc """ 162 Applies the given expression as a FILTER clause against an 163 aggregate. This is currently only supported by Postgres. 164 165 from p in Post, 166 select: avg(p.value) 167 |> filter(p.value > 0 and p.value < 100) 168 |> over(partition_by: p.category_id, order_by: p.date) 169 """ 170 171 def filter(value, filter), do: doc! [value, filter] 172 173 @doc """ 174 Returns value evaluated at the row that is the nth row of the window 175 frame (counting from 1); `nil` if no such row. 176 177 from p in Post, 178 select: nth_value(p.id, 4) |> over(partition_by: p.category_id, order_by: p.date) 179 180 Note that this function must be invoked using window function syntax. 181 """ 182 def nth_value(value, nth), do: doc! [value, nth] 183 184 @doc """ 185 Returns value evaluated at the row that is offset rows before 186 the current row within the partition. 187 188 If there is no such row, instead return default (which must be of the 189 same type as value). Both offset and default are evaluated with respect 190 to the current row. If omitted, offset defaults to 1 and default to `nil`. 191 192 from e in Events, 193 windows: [w: [partition_by: e.name, order_by: e.tick]], 194 select: { 195 e.tick, 196 e.action, 197 e.name, 198 lag(e.action) |> over(:w), # previous_action 199 lead(e.action) |> over(:w) # next_action 200 } 201 202 Note that this function must be invoked using window function syntax. 203 """ 204 def lag(value, offset \\ 1, default \\ nil), do: doc! [value, offset, default] 205 206 @doc """ 207 Returns value evaluated at the row that is offset rows after 208 the current row within the partition. 209 210 If there is no such row, instead return default (which must be of the 211 same type as value). Both offset and default are evaluated with respect 212 to the current row. If omitted, offset defaults to 1 and default to `nil`. 213 214 from e in Events, 215 windows: [w: [partition_by: e.name, order_by: e.tick]], 216 select: { 217 e.tick, 218 e.action, 219 e.name, 220 lag(e.action) |> over(:w), # previous_action 221 lead(e.action) |> over(:w) # next_action 222 } 223 224 Note that this function must be invoked using window function syntax. 225 """ 226 def lead(value, offset \\ 1, default \\ nil), do: doc! [value, offset, default] 227 228 defp doc!(_) do 229 raise "the functions in Ecto.Query.WindowAPI should not be invoked directly, " <> 230 "they serve for documentation purposes only" 231 end 232 end