<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/even-simpler-sql/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://www.johnmackintosh.com/2018-06-03-even-simpler-sql/"> HighlandR</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>I’ve had some feedback on the last post, and rather than repeat the same thing multiple times, I’m going all <a rel="nofollow" href="http://varianceexplained.org/" target="_blank">@drob</a>, and writing this instead..</p>
<p>When I tweeted out the link to my post I gave it the tag line “why I’d rather write dplyr than SQL”.</p>
<p>What I couldn’t fit in to the tweet was that this was based on the caveat that some of the SQL I have had to write has been incredibly complicated by the age / version / lack of functionality of the SQL database I was using, and the nature of the task at hand.</p>
<p>In those situations, being able to write dplyr to manipulate my data would have made my life a lot easier.</p>
<p>However, I am <strong>not</strong> against SQL.</p>
<p>Far from it, I love working with SQL and writing complex queries.</p>
<p>The more you learn, the more you understand what can be done with SQL, and it’s incredibly powerful.</p>
<p>But – there are definitely times when you think, “this would be a lot easier in R”.</p>
<h2>TL / DR :</h2>
<p><strong>SQL is great, and you should definitely learn it</strong></p>
<p><em>AND</em></p>
<p><strong>Dplyr is great, and you should definitely learn it</strong>.</p>
<p>Then you can decide which is best for the situation you are currently facing.<br>
In real life you wouldn’t need a hugely powerful database to wrangle 684 rows, and my main reason for using dplyr was that it was a small dataset and the resultant table was going to be assigned to ggplot2 for plotting purposes.</p>
<h2>Less code, same results</h2>
<p>I realised the SQL code I demonstrated for the final query was a bit convoluted, mainly because I wanted people who are new to it to be able to follow the code ( which hopefully they did).</p>
<p>However that final query could have been a lot more succinct.<br>
Here was the first part:</p>
<pre><code>SELECT * ,
	ROW_NUMBER() OVER (PARTITION BY IN_OUT, Movement_Type,Staging_Post,Movement15 ORDER BY (MovementDateTime)) * [counter] AS Movement_15_SEQNO
		FROM (
			SELECT [MovementDateTime],
					[FirstName],
				 	[LastName],
					[Ward_Dept],
					[Staging_Post],
					[Movement_Type],
					[IN_OUT],
		cast(round(floor(cast([MovementDateTime] AS float(53))*24*4)/(24*4),5) AS smalldatetime) AS Movement15,
					(CASE WHEN IN_OUT = 'IN' THEN 1 ELSE -1 END) AS [counter]
			FROM [SERVER].[dbo].[TABLENAME])x
			ORDER BY MovementDateTime
</code></pre>
<p>Having specified the necessary columns within that first query, we can simply do a</p>
<pre><code>SELECT *,
ROW_NUMBER() OVER (PARTITION BY IN_OUT, Movement_Type,Staging_Post,Movement15 ORDER BY (MovementDateTime)) * [counter] AS Movement_15_SEQNO
</code></pre>
<p>to add in the new column.</p>
<p>This time round, we are creating the row number column and multiplying it by the counter field in 1 step.</p>
<p>So this gives us a much shorter query, (as we are removing 1 level of nesting, and not specifying each column in the subsequent levels).<br>
It runs to only 14 lines, compared to the 37 in the final query last time round.<br>
Here’s the new , final version:</p>
<div>
<div>
<pre><code><span>SELECT</span> <span>*</span> <span>,</span>
	<span>ROW_NUMBER</span><span>()</span> <span>OVER</span> <span>(</span><span>PARTITION</span> <span>BY</span> <span>IN_OUT</span><span>,</span> <span>Movement_Type</span><span>,</span><span>Staging_Post</span><span>,</span><span>Movement15</span> <span>ORDER</span> <span>BY</span> <span>(</span><span>MovementDateTime</span><span>))</span> <span>*</span> <span>[</span><span>counter</span><span>]</span> <span>AS</span> <span>Movement_15_SEQNO</span>
		<span>FROM</span> <span>(</span>
			<span>SELECT</span> <span>[</span><span>MovementDateTime</span><span>],</span>
					<span>[</span><span>FirstName</span><span>],</span>
				 	<span>[</span><span>LastName</span><span>],</span>
					<span>[</span><span>Ward_Dept</span><span>],</span>
					<span>[</span><span>Staging_Post</span><span>],</span>
					<span>[</span><span>Movement_Type</span><span>],</span>
					<span>[</span><span>IN_OUT</span><span>],</span>
					<span>cast</span><span>(</span><span>round</span><span>(</span><span>floor</span><span>(</span><span>cast</span><span>([</span><span>MovementDateTime</span><span>]</span> <span>AS</span> <span>float</span><span>(</span><span>53</span><span>))</span><span>*</span><span>24</span><span>*</span><span>4</span><span>)</span><span>/</span><span>(</span><span>24</span><span>*</span><span>4</span><span>),</span><span>5</span><span>)</span> <span>AS</span> <span>smalldatetime</span><span>)</span> <span>AS</span> <span>Movement15</span><span>,</span>
					<span>(</span><span>CASE</span> <span>WHEN</span> <span>IN_OUT</span> <span>=</span> <span>'IN'</span> <span>THEN</span> <span>1</span> <span>ELSE</span> <span>-</span><span>1</span> <span>END</span><span>)</span> <span>AS</span> <span>[</span><span>counter</span><span>]</span>
			<span>FROM</span> <span>[</span><span>SERVER</span><span>].[</span><span>dbo</span><span>].[</span><span>TABLENAME</span><span>])</span><span>x</span>
			<span>ORDER</span> <span>BY</span> <span>MovementDateTime</span>
</code></pre>
</div>
</div>
<p>But wait.</p>
<p>We can also make our dplyr code even simpler.</p>
<p>One of the comments on my last post suggested that we should use if_else(), instead of case_when() for creating the counter field.<br>
And that is a great suggestion, because there are only 2 possible values that the IN_OUT column can have.</p>
<p>In addition, having created the counter field, we can make use of it straight away to create the sequence number within the same pipe.<br>
So our final dplyr code, (which works), looks like this:</p>
<div>
<div>
<pre><code><span>plot_data</span><span> </span><span>&lt;-</span><span> </span><span>data</span><span> </span><span>%>%</span><span> </span><span>mutate</span><span>(</span><span>Movement15</span><span> </span><span>=</span><span> </span><span>lubridate</span><span>::</span><span>floor_date</span><span>(</span><span>MovementDateTime</span><span>,</span><span>&quot;15 minutes&quot;</span><span>))</span><span> </span><span>%>%</span><span> </span><span>group_by</span><span>(</span><span>IN_OUT</span><span>,</span><span> </span><span>Movement_Type</span><span>,</span><span>Staging_Post</span><span>,</span><span>Movement15</span><span>)</span><span> </span><span>%>%</span><span> </span><span>mutate</span><span>(</span><span>counter</span><span> </span><span>=</span><span> </span><span>if_else</span><span>(</span><span>IN_OUT</span><span> </span><span>==</span><span> </span><span>'IN'</span><span>,</span><span>1</span><span>,</span><span>-1</span><span>),</span><span>
</span><span>Movement_15_SEQNO</span><span> </span><span>=</span><span> </span><span>cumsum</span><span>(</span><span>counter</span><span>))</span><span> </span><span>%>%</span><span> </span><span>ungroup</span><span>()</span><span> </span></code></pre>
</div>
</div>
<p>6 lines, compared to 8 in the previous example.</p>
<p>Its not really THAT big a deal for this example, but its as well to be aware that you could simplify further if you wanted to.<br>
As always, get stuff working first, then optimise it as needs be.</p>
<p>If you want really concise and powerful R code, which is even more ‘SQL like’, then you should look at <a rel="nofollow" href="https://cran.r-project.org/web/packages/data.table/" target="_blank">data.table</a>.</p>
<p>I haven’t used it a lot, but even with the short amound of time I devoted to it, I found I could write less code and see hugely impressive speed of execution , so if you get to the point where you really want to strip everything down then you will probably end up getting familiar with DT and its syntax.</p>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://www.johnmackintosh.com/2018-06-03-even-simpler-sql/"> HighlandR</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img width="1" alt="" src="http://feeds.feedburner.com/~r/RBloggers/~4/urvt9vecang" height="1"></a></div>