https://habr.com/en/company/postgrespro/blog/490228/
This is something I am interested in learning more about, particularly in SQL using WITHs, because it’s not something I’ve seen done much. I’ve learned (Oracle) SQL partly through formal training, but more practically through collaborating with colleagues and reviewing/editing/tuning queries that are part of my organization’s asset library (reports we already have published, views that I peek at under the hood and use as examples for how to accomplish something in another context, etc). That has been very helpful, but it’s also a hindrance in the case where there is a culture or habit of doing something a certain way, and you don’t get exposure to other methods, or if there isn’t much need for a particular technique. For example, I’ve taught myself to use ANSII join syntax outside my organization’s community of practice because mostly the older style is still used there, but I like the way the newer method separates the join conditions from the others.
So recursive queries using WITHs, and actually just using WITHs (common table expressions) in general is not something I see a lot in my work. Looping through a set is usually accomplished using PL/SQL or with some other procedure tool. I haven’t learned PL/SQL in my position for a few reasons, though I am thinking I should learn it, at least in part because it could be a good way to introduce myself more fully to procedural techniques in a familiar environment. I have reviewed a lot of our function packages and understand the basics, Plus, I have done some beginner’s lessons in Python, so the concepts of doing basic functions have taken root, so to speak. Though I recognize that Python is an object oriented language. Adding this here as something I’m adding to my reading list: https://www.cs.rpi.edu/~sibel/csci4380/fall2019/course_notes/sql_procedural.html
When I first started my job, years ago, the only thing I needed to understand about SQL was how to use it to create reports for our staff and faculty to use in their daily operations. Much more of my job was working with customers directly to understand what they needed and how they could effectively use the data we provided. Exception reports, pulling batches of records for reviewing and processing records, or contacting students, or loading flat files to other services or processes - that was mostly it. Querying other databases or doing more complex work was left to our developers, who essentially did “integration” stuff and advanced programming, and if we needed data to be queried alongside our Oracle data (the system of truth), the answer was often to bring a dataset into that environment in a custom table using a cron. I don’t thing that’s a bad thing, but the tools at our disposal are changing, and have been for a while. It’s now possible for someone like me - not a trained programmer, but someone who develops queries and models using SQL and application tools - to connect to a variety of sources or even use APIs to pull data into a model in seconds. That’s opened up a lot of possibilities for us to build up flexibility and capacity for meeting evolving analytics needs, but it also exposes a skill gap. I’m facing the reality that I’ve got a lot of options I don’t know how to use - or how I know to use it would be less efficient, less effective, and just more simplistic than the way an experienced programmer might approach the same thing.
Back to recursive queries with ctes - it may be that there just isn’t a lot of justification to use that technique in my current situation, which is why I haven’t seen it. Knowing that may be the case, I don’t know how much time I should even spend on learning it. And if it’s just that I’m interested in more procedural methods, or even in expanding my skills to meet current demands, does that mean I should focus elsewhere? Right now it feels like there is a lot I don’t know, and I’m not sure where I should start. I want to consider application of skills to my current job - “how will I use this in my work in the near term?” I also want to invest in the most beneficial learning plan from an objective standpoint - “regardless of where my organization is at the moment, what’s the best plan of study for leveling up generally in data science and analysis?”
Further, I’ve also got needs to meet in terms of application and platform management, as well as learning about and planning to build data architecture that supports strategic goals.
The bright side is that recently I have had some very encouraging opportunities to connect with people who have expertise in some of these areas, within as well as without my organization. Even better, when I’ve anticipated an unwelcoming response, I’ve often been pleasantly surprised. I have walked away from a lot of conversations recently feeling even more convinced of how much I don’t know, but I’ve also felt, at least some of the time, that some of the people who know more than I do have extended an invitation to me to come back and continue the conversation - they aren’t laughing behind their hands at my mistaken assumptions or blank spots.
No comments:
Post a Comment