StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I think you may consider changing your mind a bit. SQL language is very good when working with sets of data, especially modern RDBMS implementations (like SQL Server 2012), but you have to think in sets, not in rows or columns. While I stilldon't know your exact tasks, let's see - SQL Server 2012 have very nice set of <a href="http://technet.microsoft.com/en-us/library/ms189461.aspx" rel="nofollow noreferrer">window functions</a> + <a href="http://technet.microsoft.com/en-us/library/ms189798.aspx" rel="nofollow noreferrer">ranking functions</a> + <a href="http://technet.microsoft.com/en-us/library/hh213234.aspx" rel="nofollow noreferrer">analytic functions</a> + <a href="http://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx" rel="nofollow noreferrer">common table expressions</a>, so you can write almost any query inline. You can use chains of <a href="http://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx" rel="nofollow noreferrer">common table expression</a> to turn your data any way you want, to calculate running totals, to calculate averages or other aggregates over window and so on.</p> <p>Actually, I've always liked SQL and when I've learned functional language (ML and Scala) a bit, my thought was that my approach to SQL is very similar to functional language paradigm - just slicing and dicing data without saving anything into variables, untils you have resultset your need.</p> <p>Just <strong>quick example</strong>, here's a question from SO - <a href="https://stackoverflow.com/questions/19100772/how-to-get-average-of-the-middle-values-in-a-group">How to get average of the 'middle' values in a group?</a>. The goal was to get the average for each group of the middle 3 values:</p> <pre><code>TEST_ID TEST_VALUE GROUP_ID 1 5 1 -+ 2 10 1 +- these values for group_id = 1 3 15 1 -+ 4 25 2 -+ 5 35 2 +- these values for group_id = 2 6 5 2 -+ 7 15 2 8 25 3 9 45 3 -+ 10 55 3 +- these values for group_id = 3 11 15 3 -+ 12 5 3 13 25 3 14 45 4 +- this value for group_id = 4 </code></pre> <p>For me, it's not an easy task to do in R, but in SQL it could be a really simple query like this:</p> <pre><code>with cte as ( select *, row_number() over(partition by group_id order by test_value) as rn, count(*) over(partition by group_id) as cnt from test ) select group_id, avg(test_value) from cte where cnt <= 3 or (rn >= cnt / 2 - 1 and rn <= cnt / 2 + 1) group by group_id </code></pre> <p>You can also easily expand this query to get 5 values around the middle.</p> <p>TAke closer look to <a href="http://technet.microsoft.com/en-us/library/hh213234.aspx" rel="nofollow noreferrer">analytical functions</a>, try to rethink your calculations in terms of window functions, may be it's not so hard to rewrite your R procedures in plain SQL.</p> <p>Hope it helps.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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