![]() ![]() Regr_intercept ( Y double precision, X double precision ) → double precisionĬomputes the y-intercept of the least-squares-fit linear equation determined by the ( X, Y) pairs. Regr_count ( Y double precision, X double precision ) → bigintĬomputes the number of rows in which both inputs are non-null. Regr_avgy ( Y double precision, X double precision ) → double precisionĬomputes the average of the dependent variable, sum( Y)/ N. Regr_avgx ( Y double precision, X double precision ) → double precisionĬomputes the average of the independent variable, sum( X)/ N. SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab īeware that this approach can fail if the outer query level contains additional processing, such as a join, because that might cause the subquery's output to be reordered before the aggregate is computed.Ĭorr ( Y double precision, X double precision ) → double precisionĬovar_pop ( Y double precision, X double precision ) → double precisionĬovar_samp ( Y double precision, X double precision ) → double precision Alternatively, supplying the input values from a sorted subquery will usually work. This ordering is unspecified by default, but can be controlled by writing an ORDER BY clause within the aggregate call, as shown in Section 4.2.7. ![]() The aggregate functions array_agg, json_agg, jsonb_agg, json_object_agg, jsonb_object_agg, string_agg, and xmlagg, as well as similar user-defined aggregate functions, produce meaningfully different result values depending on the order of the input values. The coalesce function can be used to substitute zero or an empty array for null when necessary. In particular, sum of no rows returns null, not zero as one might expect, and array_agg returns null rather than an empty array when there are no input rows. It should be noted that except for count, these functions return a null value when no rows are selected. Sum ( double precision ) → double precisionĬomputes the sum of the non-null input values.Ĭoncatenates the non-null XML input values (see Section 9.15.1.7). Each value after the first is preceded by the corresponding delimiter (if it's not null). String_agg ( value bytea, delimiter bytea ) → byteaĬoncatenates the non-null input values into a string. String_agg ( value text, delimiter text ) → text Range_intersect_agg ( value anymultirange ) → anymultirangeĬomputes the intersection of the non-null input values. Range_intersect_agg ( value anyrange ) → anyrange Range_agg ( value anymultirange ) → anymultirangeĬomputes the union of the non-null input values. Range_agg ( value anyrange ) → anymultirange Available for any numeric, string, date/time, or enum type, as well as inet, interval, money, oid, pg_lsn, tid, xid8, and arrays of any of these types. Available for any numeric, string, date/time, or enum type, as well as inet, interval, money, oid, pg_lsn, tid, xid8, and arrays of any of these types.Ĭomputes the minimum of the non-null input values. Values can be null, but not keys.Ĭomputes the maximum of the non-null input values. Key arguments are coerced to text value arguments are converted as per to_json or to_jsonb. Jsonb_object_agg ( key "any", value "any" ) → jsonbĬollects all the key/value pairs into a JSON object. Json_object_agg ( key "any", value "any" ) → json Values are converted to JSON as per to_json or to_jsonb. This is the SQL standard's equivalent to bool_and.Ĭollects all the input values, including nulls, into a JSON array. Returns true if any non-null input value is true, otherwise false.Ĭomputes the number of input rows in which the input value is not null. Returns true if all non-null input values are true, otherwise false. Can be useful as a checksum for an unordered set of values. ![]() (The inputs must all have the same dimensionality, and cannot be empty or null.)Īvg ( double precision ) → double precisionĬomputes the average (arithmetic mean) of all the non-null input values.Ĭomputes the bitwise AND of all non-null input values.Ĭomputes the bitwise OR of all non-null input values.Ĭomputes the bitwise exclusive OR of all non-null input values. Collects all the input values, including nulls, into an array.Ĭoncatenates all the input arrays into an array of one higher dimension. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |