Press "Enter" to skip to content

Curated SQL Posts

Azure Synapse Analytics January 2023 Updates

Published 2023-02-09 by Kevin Feasel

Ryan Majidimehr shares an overview of what the Synapse team has been working on:

ADX contains native support for detecting anomalies over multiple time series by using the function series_decompose_anomalies(). This function can analyze thousands of time series in seconds, enabling near real time monitoring solutions and workflows based on ADX. Univariate analysis is simpler, faster, and easily scalable and is applicable to most real-life scenarios. However, there are some cases where it might miss anomalies that can only be detected by analyzing multiple metrics at the same time.

For some scenarios, there might be a need for a true multivariate model, jointly analyzing multiple metrics. This can be achieved now in ADX using the new Python-based multivariate anomaly detection functions.

The themes for this month are Spark, Data Exploration (via Kusto), and data integration.

Comments closed

Approximation with the Mediant

Published 2023-02-08 by Kevin Feasel

John Cook didn’t make a typo:

Suppose you are trying to approximate some number x and you’ve got it sandwiched between two rational numbers:

a/b < x < c/d.

Now you’d like a better approximation. What would you do?

The obvious approach would be to take the average of a/b and c/d. That’s fine, except it could be a fair amount of work if you’re doing this in your head.

Read on for a separate approach taking the mediant (not median) of the two fractions.

Comments closed

Splitting Strings to a Table in Snowflake

Published 2023-02-08 by Kevin Feasel

Kevin Wilkie puts on the flannel and grabs his database lumberjack axe to split some strings:

But, sometimes, you want a small list of data inherent to a query in SnowFlake. And that’s what I want to talk about today.

In SQL Server, you would create a temp table and then insert the data into it. But in Snowflake, there may be a better / easier way.

Let’s use the function SPLIT_TO_TABLE. Shockingly, it does what’s on the label – it split data and puts it into a table.

Click through for an example. Also check out the Snowflake documentation, where they make use of the lateral operator (the ANSI version of APPLY()) to generate results for multiple strings and make use of the SEQ column.

Comments closed

FILTER vs CASE in Postgres

Published 2023-02-08 by Kevin Feasel

Lukas Eder hits on an interesting data point:

I’ve found an interesting question on Twitter, recently. Is there any performance impact of using FILTER in SQL (PostgreSQL, specifically), or is it just syntax sugar for a CASE expression in an aggregate function?

Click through for the answer and your daily reminder that SQL variants aren’t pure fourth generation languages—if they were, the optimizer would take all possible constructs of a given desired operation and generate the same execution plan for all of them.

Comments closed

Referencing an Intermediate Step in Power Query

Published 2023-02-08 by Kevin Feasel

Imke Feldmann has a clever solution to a problem:

When you reference another query in Power Query you will automatically get the results of the final step. But what if you want to reference a step that sits within that referenced query?

One solution would be to split up that query into 2 and reference the final result of the splitted query. But that could result in additional query time, as the data might be pulled from the source twice (once for each query).

Read on for a better way to do it.

Comments closed

Diagnosing VSCode + R Integration Problems

Published 2023-02-07 by Kevin Feasel

John MacKintosh troubleshoots an issue:

I recently updated my R installation, and then realised that I’d broken my VSCode/ R set up in the process – I could not launch an R terminal either directly or via radian.

I have a repo where I’ve collated various blog posts relating to setting up VSCode for R, but that didn’t solve all my problems.

I did get it resolved eventually, and here’s how.

Read on for that resolution. H/T R-Bloggers.

Comments closed