Databases
StackOverflow2013
Postsdbo
PODB2 SQL - median with GROUP BY
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODB2 SQL - median with GROUP BY
    primarykey
    data
    text
    <p>First of all, I am running on DB2 for i5/OS V5R4. I have ROW_NUMBER(), RANK() and common table expressions. I do <b>not</b> have TOP n PERCENT or LIMIT OFFSET.</p> <p>The actual data set I'm working with is hard to explain, so let's just say I have a weather history table where the columns are <code>(city, temperature, timestamp)</code>. I want to compare medians to averages for each group <code>(city)</code>.</p> <p>This was the cleanest way I found to get a median for a whole table aggregation. I adapted it from the IBM Redbook <a href="http://www.redbooks.ibm.com/redbooks/pdfs/sg246546.pdf" rel="nofollow noreferrer">here</a>:</p> <pre><code>WITH base_t AS ( SELECT temp, row_number() over (order by temperature) AS rownum FROM t ), count_t AS ( SELECT COUNT(temperature) + 1 AS base_count FROM base_t ), median_t AS ( SELECT temperature FROM base_t, count_t WHERE rownum in (FLOOR(base_count/2e0), CEILING(base_count/2e0)) ) SELECT DECIMAL(AVG(temperature),10,2) AS median FROM median_t </code></pre> <p>That works well for getting a single row back, but it seems to fall apart for grouping. Conceptually, this is what I want: </p> <pre><code> SELECT city, AVG(temperature), MEDIAN(temperature) FROM ... </code> city | mean_temp | median_temp =================================================== 'Minneapolis' | 60 | 64 'Milwaukee' | 65 | 66 'Muskegon' | 70 | 61 </pre> <p>There could be an answer that makes me look stupid, but I'm having a mental block and this isn't my #1 thing to work on right now. Seems like it could be possible, but I can't use something that's extremely complex since it's a large table and I want the ability to customize which columns are being aggregated.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. UStwblamer
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload