title: What is the fatest way of calculating summary statistics in SQL when comparing these two queries?
body: <p>I have a table on PostgreSQL having the columns: <code>date</code>, <code>col1</code>, <code>condition</code>. I would like to calculate the statistical summary of <code>col1</code> segmenting by <code>date</code> and <code>condition</code>. To this end, I wrote the following query</p>
  <pre><code>SELECT date
        ,col1
        ,condition
        ,COUNT(*)
        ,SUM(col1)
        ,MAX(col1)
        ,MIN(col1)
        ,AVG(col1)
        ,STDDEV(col1)
        ,PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY col1) AS &quot;Q3&quot;
        ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col1) AS &quot;Median&quot;
        ,PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY col1) AS &quot;Q1&quot;
  FROM table
  GROUP BY date, condition
  </code></pre>
  <p>I could also write the same query as</p>
  <pre><code>    SELECT date
        ,col1
        ,condition
        ,COUNT(*) OVER(PARTITION BY date, condition)
        ,SUM(col1) OVER(PARTITION BY date, condition)
        ,MAX(col1) OVER(PARTITION BY date, condition)
        ,MIN(col1) OVER(PARTITION BY date, condition)
        ,AVG(col1) OVER(PARTITION BY date, condition)
        ,STDDEV(col1) OVER(PARTITION BY date, condition)
        ,PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY col1) OVER (PARTITION BY date, condition) AS &quot;Q3&quot;
        ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col1) OVER (PARTITION BY date, condition) AS &quot;Median&quot;
        ,PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY col1) OVER (PARTITION BY date, condition) AS &quot;Q1&quot;
  FROM table
  GROUP BY date, condition
  </code></pre>
  <p>Which one of these queries would be the fastest? For my own (brief) experience, the second seems faster. However, running the clause <code>OVER(PARTITION BY date, condition)</code> should slow it?</p>
  <p>I have not a good knowledge of SQL and an explanation would help to me to write the queries more efficiently.</p>