SQL Snippets useful for analysis, analytics and data mining.

While there are plenty of resources for SQL out there, my aim is to provide you with uncommon SQL examples that will aid you in your analytic endeavors.

If you are using Clementine for data mining, you probably know that Clementine manipulate data just like SQL and even beyond that to.  No doubt this is a great feature about Clementine.  Having a data mining workbench with all tools at your disposal is superb.  However you also probably realize that your data mining no longer has to be conducted on a sample of your sample population but rather your analysis can be conducted on your entire sample population and even on the entire population which can be milions and millions of records.   This holds true for more and more businesses as we move away from flat files and are querying directly from databases. Clementine, being that it resides on your PC will intelligently allocate resources in your stream processing, but processing millions of records and invoking all sorts of transformations will be taxing on your machine, possibly to the point where it freezes.   If not, it will definitely increase the time Clementine takes in processing data and providing you with the final output.

But if data was prepped to a degree where SPSS clementine did not have to do so much simple processing that can be delegated to the database, your stream's performance would certainly increase.  A lower load on your machine also translates to a higher life expectancy of your work station and more efficiently produced results.

Well that said, now we know why we want to do as much as the straight forward and even sometimes complex data preparation tasks at the database/SQL level as opposed to having Clementine or any other analytical tool handle it.   Sas is another tool where you can create many temporary data sets and manipulate data with, but why write all that code and add to your machines load when you can write some SQL and place the load on the database, which is better equipped to handle it.