![]() The bool_or( expression) aggregate returns true if expression is true for any row, false otherwise. ![]() ![]() With COUNT( boolean-expression OR NULL), we can count only those rows for which a boolean expression is true, since boolean-expression OR NULL evaluates to null when the boolean expression is false:ĬOUNT(type = 'purchase-see-offer' OR NULL),ĬOUNT(type = 'purchase-complete' OR NULL)ĭetermining Whether Any Row Satisfies A Condition The COUNT( expression) aggregate function counts only those rows for which expression is not null. SELECT type, COUNT(*) FROM events GROUP BY type For example, I can find out how many rows there are for each type of event in the events table: When used with a GROUP BY, aggregate functions apply to each group separately. For example, I can find out how many rows are in the events table: Official Documentation of built-in window functions Details Aggregates Aggregating Over A TableĪn aggregate function yields a single value from a bunch of rows. Official Documentation about window functions Ntile(2) OVER (ORDER BY column) AS bucket Note: This implementation does not provide the true median since, for simplicity, it does not average the two middle values when the row count is even. Official Documentation (Approximate) Median True if expression is true for any row, false otherwise The number of rows where expression is true The number of rows where expression is not null Official Documentation Aggregates count( expression) Round to the specified number of decimal places Turn off paging, to print all results without the need for manual scrolling Toggle extended format, which gives each value more room: its own line instead of the typical tabular display Here’s a compilation of things I learned or used frequently. But with the tens of millions of rows I needed to crunch for analytics, I achieved significantly better performance and clearer code by writing raw SQL. Often I hit the database through Ruby on Rails’ ActiveRecord ORM. Recently, I have been working with analytics in PostgreSQL.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |