3 ms·
tldr ; if you hear ''but we can do this in SQL'', RUN!!! My eyes hurt as I read this article. There are reasons why analysts dont use SQL to do their job, and
by jplr8922 5y ago
tldr ; if you hear ''but we can do this in SQL'', RUN!!!
My eyes hurt as I read this article. There are reasons why analysts dont use SQL to do their job, and it has nothing to do with saving RAM and memory.
1) Data analysis is not a linear process, it involve playing and manipulating the data in different way and letting your mind drift a bit. You want your project in an IDE made for that purpose, the ability to create charts, source control, export and share the information, ect. Pandas is just a piece of that puzzle which is not possible to replicate in pure SQL.
2) In 2020, there are numerical methods you want to try beyond a traditional regression. Most real world data problems are not made for stats101 tools included in sql. Kurtosis? Autocorrelation?
3) Politics. Most database administrator are control freaks who hate the idea of somebody else doing stuff in their DB. Right now were I work we still have to use SSIS-2013 instead of stored procedures in order to avoid the DBA refusal bureaucratic process.
4) Eventual professional development. If your analysis is good and creates value, chances are it will become a 'real' program and you will have to explain what you are doing to turn in into an OOP tool. If you have CS101, good coding in python will make this process much easier than a 3000 lines spagetti-SQL SQL script.
5) Data cleaning. Dealing with outliers, NAN and all that jazz really depends on the problem you try to solve. The absence of a one size fits all solutions is a good case for R/pandas/etc. These issue will break an SQL script in no time.
6) Debugging in SQL. Hahahahahahahaha
If you are still preocupied with the ram usage of your PC to do your project, here are two solutions which infuriate a lot of DBAs I've worked with.
A) https://www.amazon.ca/s?k=ram&__mk_fr_CA=%C3%85M%C3%85%C5%BD%C3%95%C3%91&ref=nb_sb_noss_2 https://www.amazon.ca/s?k=ram&__mk_fr_CA=%C3%85M%C3%85%C5%BD...
B) https://aws.amazon.com/ https://aws.amazon.com/
- qwertox 5y agoWhat is your problem with this example, doesn't it get the job done? WITH temperatures AS ( /* ... */ ) SELECT *, MAX(c) OVER ( ORDER BY t ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS hottest_temperature_last_three_days FROM temperatures; t │ c │ hottest_temperature_last_three_days ────────────┼────┼───────────────────────────────────── 2021-01-01 │ 10 │ 10 2021-01-02 │ 12 │ 12 2021-01-03 │ 13 │ 13 2021-01-04 │ 14 │ 14 2021-01-05 │ 18 │ 18 2021-01-06 │ 15 │ 18 2021-01-07 │ 16 │ 18 2021-01-08 │ 17 │ 17 Why should I fetch all the data and then form the result with another tool? What a great article.
- jplr8922 5y agoI re-read my comment, and I think that I expressed myself too harshly. If you like SQL and it get the job done for you, no problem. I would see myself using that SQL query. However in order to get there, you need to know why you need the ''hottest_temparature_last_three_days''. Why not 2 or 4 days? Why not using heating degree day? What about serial correlation with other regions, or metering disfunction? What if you are working directly with raw data from instruments, and still need to choose wich cleaning method you will use? What if you want to check the correlation with another dataset which is not yet in your database (ex: private dataset in excel from a potential vendor)? If you know exactly what you want, sure SQL is the way to go. However the first step of a data project is to admit that you dont know what you want. You will perform a litterature review and might have a general idea, but starting with a precise solution in mind is a receipe for failure. This is why you could need to fetch all the data and then form results with another tool. How do you even know which factors and features must be extracted before doing some exploration? If you know the process you need and only care about RAM-CPU optimization, sure SQL is the way to go. Your project is an ETL and your have the job of a programmer who is coding a report. There is no ''data analysis'' there...
- sagarm 5y agoIf you know SQL well, then doing exploratory analysis in SQL is perfectly reasonable. Especially if you are using a high-performance analytic database. Disclosure: I develop high-performance analytic database systems with SQL frontends.
- jplr8922 5y agoWhat do you mean by exploratory analysis? Lets assume that my dataset contains features which are not normally distributed, and I want to check for coskewness and cokurtosis matrices before and after I remove outliers with a method specific to my field of research, found in a academic paper. Am I supposed to code all of this in SQL? What is your definition of exporatory analysis, and which metrics do you have in mind? And what if I want to make graphs to present stuff to a colleague? Am I supposed to use... excel to do that?
- kbelder 5y agoFamiliar. I think the biggest issue is understanding workflow. My hunch is that some developers view a data analysis project as a program... it needs to have these data processing steps performed, summarized in this certain way, and saved off in that certain format. Once those queries are defined, the program is ran and the analysis done. The actual work the analyst is doing is far more ad-hoc than that. It's iterative and frequently will get torn down, rebuilt, and joined to other sources, as they discover new aspects to the data. They need their hands on the raw data, not on a specifically summarized version of it.
- jplr8922 5y agoYup. That is exactly it. You cannot believe the number of programmer I've met who thinks that ''data science is easy'' because ''I can code a neural net too''. Data analysis is a soft skill that you usually learn by getting your hands dirty in a graduate or professional environment. Its not something you can put on a CV like like ''X years of experience with Y programming language''.