Advanced Analytics with R and SQL Part II - Data Science Scenarios (SQLServerCentral 7/27/2017)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [Advanced Analytics with R and SQL Part II - Data Science Scenarios]( - [Statistics in SQL: The Kruskal–Wallis Test]( - [Default reports in SSMS]( (From the SQLServerCentral Blogs) - [SQL Server 2017 – DMV – sys.dm_os_host_info]( (From the SQLServerCentral Blogs) The Voice of the DBA Who's Smarter? Humans or AI Systems? I used to watch science fiction movies and imagine we'd have these incredible machines that could interact with humans in amazing ways. I always believed more in the [helpful and useful AI in Star Wars]( than the dangerous systems of movies like [The Terminator](character_concept)) or [The Matrix](. I could even imagine a time when we have sentient systems, though I'm not sure they are coming anytime soon. I used to assume the [Asimov's Laws of Robotics]( would be implemented without any issue. After all, doesn't everyone worry about security? These days I'm more cynical, and I wonder if anyone would actually bother to program limitations in a system. I expect more people would assume we'll add in the laws later, once we know the other features are working. I do think we will get more and more systems like [IBM's Watson](, that are designed to handle many tasks. When I read pieces like [this one from HBR](, I actually get a little worried. Not because machines will destroy humans, but they will greatly impact and change our society, potentially in disruptive ways for society. While we've seen issues with manufacturing over the years, there are still humans involved, and we have learned to find other jobs over time. The AI revolution, if that's what's happening, will occur quicker, and if the HBR piece is correct, will impact many more people, across a shorter period of time. Our economies and workforce may not adapt as quickly, which has a variety of economic implications for all of us, even those of us that might be secure in our positions. If we can teach systems to accomplish tasks at a level even close to what the average human can do, we might find ourselves either out of a job or working with some sort of robot. I don't know how quickly we'll advance, or even how far we will go, but I do find that the leverage offered by implementing advanced technology is certainly increasing in a way that worries me. Steve Jones from [SQLServerCentral.com]( Join the debate, and [respond to today's editorial on the forums]( --------------------------------------------------------------- The Voice of the DBA Podcast Listen to the [MP3 Audio]( ( 3.3MB) podcast or subscribe to the feed at [iTunes]( and [Libsyn](. [feed]( The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. ADVERTISEMENT [Foundry]( How do you manage sensitive data? Redgate's Foundry team are researching data discovery and classification. Fill out this short survey if you think you can help, and at the end you'll be entered into a prize draw. [Fill out survey]( [SQL Clone]( SQL Clone: Now supporting databases up to 64TB Create copies of production databases and SQL backups in seconds and save up to 99% of disk space using SQL Clone. Redgate’s new tool removes much of the time and resource needed to create and manage database copies, allowing teams to work on local environments to develop, test and diagnose issues faster. [Try it free]( Featured Contents  [] [Advanced Analytics with R and SQL Part II - Data Science Scenarios]( Frank A. Banin from [SQLServerCentral.com]() Build and Operationalize scalable Predictive models and intelligent applications using SQL and R. [More »](Science/158498/) ---------------------------------------------------------------  [] [Statistics in SQL: The Kruskal–Wallis Test]( Additional Articles from [SimpleTalk]() Before you report your conclusions about your data, have you checked whether your 'actionable' figures occurred by chance? The Kruskal-Wallis test is a safe way of determining whether samples come from the same population, because it is simple and doesn't rely on a normal distribution in the population. This allows you a measure of confidence that your results are 'significant'. Phil Factor explains how to do it.[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Default reports in SSMS]( Kenneth Fisher from [SQLServerCentral Blogs]( As DBAs our stock in trade is information and there is certainly an impressive amount available. The diagnostic views are...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [SQL Server 2017 – DMV – sys.dm_os_host_info]( Dharmendra Keshari from [SQLServerCentral Blogs]( I always love to get my hands dirty with new features of SQL Server. I was exploring SQL Server on...[More »]( Question of the Day Today's Question (by Steve Jones): I have a set of basketball statistics and I want to analyze a player. I'm hoping to get the number of points that a player scored and the difference from his first year with that team. For J.R. Reid (against whom I played as a kid), I have this data: year team pts ------- -------- -------------------------------------------------- 1989 CHA 908 1990 CHA 902 1991 CHA 560 1992 CHA 127 1992 TOT 780 1992 SAS 653 1993 SAS 627 1994 SAS 563 1995 SAS 208 1995 TOT 427 1995 NYK 219 I have this query with first_Value(), but it doesn't seem to work. Why not? SELECT TOP 10 year, team, CAST(pts AS INT) AS 'Season Points', CAST(pts AS INT) - FIRST_VALUE(pts) OVER (ORDER BY (SELECT NULL) ) AS 'Difference from first year with team' FROM dbo.player_regular_season WHERE firstname = 'J.R.' AND lastname = 'Reid' ORDER BY [year]; The results of my query: year team Season Points Difference from first year with team ------ ----- ------------- ------------------------------------ 1989 CHA 908 0 1990 CHA 902 -6 1991 CHA 560 -348 1992 CHA 127 -781 1992 TOT 780 -128 1992 SAS 653 -255 1993 SAS 627 -281 1994 SAS 563 -345 1995 SAS 208 -700 1995 TOT 427 -481 Think you know the answer? [Click here](, and find out if you are right. --------------------------------------------------------------- We keep track of your score to give you bragging rights against your peers. This question is worth 2 points in this category: FIRST_VALUE. We'd love to give you credit for your own question and answer. To submit a QOTD, simply log in to the [Contribution Center](. ADVERTISEMENT [SQL Server T-SQL Recipes]( SQL Server T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server. This edition has been lightly updated for SQL Server 2014 and provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, create in-memory tables and stored procedures, insert and update data, generate reports, secure your data, and more. [Get your copy from Amazon today](. ADVERTISEMENT [Pro Power BI Desktop]( This book shows how to deliver eye-catching Business Intelligence with Microsoft Power BI Desktop. You can now take data from virtually any source and use it to produce stunning dashboards and compelling reports that will seize your audience’s attention. Slice and dice the data with remarkable ease then add metrics and KPIs to project the insights that create your competitive advantage. Yesterday's Question of the Day Yesterday's Question (by Steve Jones): For these questions, I am working with RStudio without any additional packages installed. I want to plot the number of Home Runs for Hank Aaron. I create a vector with the CU count: HankAaron <- c(13,27,26,44,30,39,40,34,45,44,24,32,44,39,29,44,38,47,34,40,20,12,10) Now I to graph his home runs over time showing the count of each relative to the others. However, I want to label the graph with the year of each count. I have this vector: Years <- c(1954:1976) What should I run before I do a barplot(HankAaron) to get an image like this? Answer: names(HankAaron) <- Years Explanation: The names() function will assign labels to the indexes of a vector. Use this to have your labels be listed in a plot. Ref: names() - [click here]( --------------------------------------------------------------- [» Discuss this question and answer on the forums]( Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Administration]( [Getting NULL Values after execution using sp_executesql]( - I am trying to execute the command in the other server which are linked. EXEC @retVal = sp_executesql @dml; statement giving me... ["Network-related or Instance specific error" when connecting through SSMS]( - This is the 1st time I came across this issue where I am unable to access the instance from SSMS... [After sql upgrade from sql 2014 to sql 2016 failover on 2 node windows cluster fails]( - I have a 2 node windows cluster set up.  node 1 (active node) fails to roll over to node 2... [SQL Server geo-cluster 2016]( - Dear Everyone Ikeep reading online that its not recommended to virtualize SQL Server clusters (4 nodes)  --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Development and T-SQL]( [Cursor Performance]( - OK so we have an application that has gone through an upgrade which also saw the database move from a... --------------------------------------------------------------- [SQL Server 2014]( : [Administration - SQL Server 2014]( [need list of logins at the server level]( - My issue is we removed several users at the server level and left them at the database level. I'm trying... [restore point]( - Is there something equivalent in SQL server like create guaranteed restore point in oracle? I know in SQL server if we... --------------------------------------------------------------- [SQL Server 2012]( : [SQL 2012 - General]( [SQL Server 2012 Express edition installation error!]( - Hello, I'm urgently trying to install SQL server express onto my Windows 7 laptop (x64) but keep receiving the setup error... [List of all dtsx files used in - I need help with the SELECT statement]( - How may I write a SELECT statement that will list each SSIS Package and the dtsx files that go with... [Difference between (where ID = 543 vs Where ID = '543')]( - Is there any advantages, disadvantages, system overhead etc if let's say I use a '543' vs 543 in a where... [Outer Apply - Alternative]( - Hi I am trying to utilize the below query but without using otter apply Can someone help me out with an alternative? Thanks, [code... [SQL Agent Job History shows step still running but shows start and end times]( - Hi all, I have a SQL Agent job that runs at 4:15 in the morning. The job has 5 steps, each... --------------------------------------------------------------- [SQL Server 2012]( : [SQL Server 2012 - T-SQL]( [How to get top and bottom rows from where condition]( - Hi, Create Table Test ( Id int, name varchar(80) ) Insert into Test Select 1, 'A' union All Select 2, 'B' union All --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server 2008 - General]( [What is the purpose of dropping temp db?]( - Is there any advantage to test for temp db's in a stored procedure and then drop them as in this... [Expensive View help]( - Hi Guys I have the below query which is part of a view. This query takes > 5 minutes to complete.... [snapshot restores getting slower]( - Hi, in one of our test environments I've noticed snapshots restores are taking 4 x the amount of time they... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server Newbies]( [Recovery Pending Status]( - Good Morning Experts, One of our databases went into "Recovery Pending" Status. Could you please share the steps to fix it. --------------------------------------------------------------- [Data Warehousing]( : [Integration Services]( [two decimal points issue]( - Hi I have an excel document with a column called Code this column is formatted to General there are codes... --------------------------------------------------------------- [SQL Server 2005]( : [Administering]( [Roles of development DBA]( - Hi, I am new to MS SQL Server. I like to know what are the roles of sql server dba in... --------------------------------------------------------------- [SQL Server 2005]( : [SQL Server 2005 Integration Services]( [Send a email if a file has not loaded 7 days afetr the 24 of the month]( - Every month on the 24th we receive a invoice for the months purchases. It is always generated for the 25th through... This email has been sent to {EMAIL}. To be removed from this list, please click [here.]( If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. --------------------------------------------------------------- This newsletter was sent to you because you signed up at [SQLServerCentral.com](. Feel free to forward this to any colleagues that you think might be interested. If you have received this email from a colleague, you can register to receive it [here](. --------------------------------------------------------------- This transmission is ©2017 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com