Using LLMs to write pattern-matching SQL
Posted by sqlpete in artificial intelligence, coding on October 8, 2024
Thirty second summary
At a previous company, we had a database table that contained important data, held as semi-structured text. The existing code to extract this data was not very neat, but it worked. Using dummy data, I asked LLMs (GPT-4o and Claude 3.5 Sonnet) to write the code from scratch to be smaller and more configurable, but this wasn’t completely successful. In the end, I had to write the code myself, although Claude was useful in a couple of situations.
I’ve written about this before: a previous company had a database table of customer-related notes, a repository of ‘stuff’ that didn’t live nicely anywhere else (at least, according to the developers who built the system). Some of the text was auto-generated (“Customer X changed their address from Y to Z”), some was written by customer-facing agents (“Just spoke to Bob on the phone, they will be changing bank accounts next week.”). It turned out that this table was the only source of some very valuable historics, so I had a process that parsed this data out into better-defined, normalised tables.
The code was reasonably simple: it looked for strings that matched, e.g. ‘Customer % changed home address % to %’ (where ‘%’ is a wild-card, matching anything), then pulled the relevant data points out using CHARINDEX, PATINDEX and SUBSTRING functions. Overall, it wasn’t compact, but it was a doddle to maintain and extend, as the code was so straightforward.
I had always wanted to re-write the code to be ‘cleverer’, but never had the time, and it’s been niggling away at the back of my brain for ages. So, with some downtime available to me, I decided to have a bash. But also, I wanted to see if LLMs could do the work for me!
Here’s some dummy data of the kind we were dealing with:
Customer Andrew Beadle has a customer number of 1234567 and a score of 27.
Customer Charlie Duntz has a customer number of 234567 and a score of 381.
Customer Eric Fowlds has a customer number of 11998 and a score of 2881.
Customer Georgie Hertz has a customer number of 0003 and a score of 4.
Supplier Ian Jones has a supplier number of 1313 and a postcode of NH2 4JK.
Supplier Kevin Leary has a supplier number of 3134 and a postcode of B1 5QQ.
Supplier Monica Nawson has a supplier number of 9876 and a postcode of W1A 8WX.
Buyer Ollie Pearson works for BigCorp Ltd and has a total spend of £10000000, up to date 2024-06-03.
Buyer Quentin Rawles works for MassiveCo and has a total spend of $20000000, up to date 2024-03-13.
Buyer Susan Trantor works for Leeds City Council and has a total spend of £50000000, up to date 2025-11-30.
And here are three templates that I want to check the dummy input against, and pull out the relevant data points:
Customer % has a customer number of % and a score of %.
Supplier % has a supplier number of % and a postcode of %.
Buyer % works for % and has a total spend of %, up to date %.
Note that I could go further, e.g. match dates explicitly with a more complex pattern like ‘[12][0-9][0-9][0-9]-[01][0-9]-[0123][0-9]’, but to be honest, in the real application, the dates were so often mangled (different developer, different date format!) that we had extra steps, post-matching, to cope with situations like this.
Using LLMs to do the work
Currently, my two ‘go-to’ LLMs are Anthropic’s Claude 3.5 Sonnet (accessed here) and OpenAI’s GPT-4o (accessed here). Both are free for a certain number of queries per day; I occasionally hit their limits, but not often.
(Note: I did try Microsoft Copilot, but it uses GPT-4o under the hood, and it returned almost identical results. Also, I wanted to use GPT’s new o1 model, but I didn’t have access at the time.)
Could either of them write my code for me? Spoiler: no, not really.
I put the same prompt into both LLMs, along the lines of “I have this data : {data as above}, and these patterns: {patterns as above}. Please can you write T-SQL code that will extract the indicated parts of the strings, where the entire pattern matches an input string?”
Claude 3.5 Sonnet
Of the two, Claude did much better; it understood that I didn’t want any hard-coded strings in the code, it had to parse the templates. The code looked correct, but failed to run. I pasted error messages back in, and got revised code; however, the code just grew without ever running. After some manipulation, I worked out that if I cut the prompt back to looking for a single matching string (instead of the three), then with some light modification, I could get the code to work. Eventually, I used parts of this successful approach in my own solution, so it wasn’t a waste of time.
One strange thing: at one point, of its own volition, it introduced the concept of ‘stop words’, and tried to filter out certain words from the output. I didn’t ask for this, or anything remotely like this, and told it so; it apologised and removed the code!
GPT-4o
This was a bust from the off: it started generating a sproc, but it was hard-coding text, and it ended up returning hundreds of lines of generally unreadable code. Even when I pointed out it hadn’t followed the instructions, and added “The stored procedure must only be driven by the data in the tables, absolutely no text from the patterns can be hard-coded” to the prompt, it didn’t ‘understand’ well enough to remove the hard-coding. I couldn’t get it to work with any amount of repeated prompting.
My code
In the end, I re-wrote the code myself, using a hint or two from Claude’s failed attempt. My code, using the dummy data/patterns from above, is viewable in this PDF document: my_pattern_matching_code.pdf (240 KB).
XML output
For fun, I wanted to output my new recordset as XML. Instead of writing this block of code myself, I mocked up a dummy output, and asked Claude to come up with the code. It took a little back and forth, but it worked in the end, and probably took less time than it would if I’d battled through myself! (I can never remember the finer points of the syntax for creating XML within SQL, and maybe now I’ll never have to!) The XML output looks like the following:
(The SQL code to generate the XML is in the PDF document above)
If I wanted, I could apply XSLTs to the XML output, and do any sort of processing before I store the data.
Wrap-up
So, LLMs are great, but not perfect — the fact that they couldn’t fully complete the task here would be no big surprise to those of us who use them daily to help with coding. I was quite shocked by Claude hallucinating the requirement for ‘stop’ words. Maybe if I’d had access to OpenAI’s o1-preview or o1-mini then this little experiment might have worked.
Of course, it’s always a possibility that my prompts aren’t up to scratch, and with more work, I could get the results I was after. I am not a Prompt Engineer!
Finally, just because I was asked the other day, here are some of the people I follow for AI news:
- Matt Wolfe: Youtube: @mreflow / twitter: @mreflow
- Matthew Berman: Youtube: @matthew_berman / twitter: @MatthewBerman
- Two Minute Papers: Youtube: @TwoMinutePapers / twitter: @twominutepapers
- AI Explained: Youtube: @aiexplained-official / @aiexplainedyt
Estimating the true gini from a confusion matrix
Posted by sqlpete in credit risk, stats on September 24, 2024
Thirty second summary
The gini value is a measure, from 0 (no better than random guessing) to 1 (perfect), of how good a model is at predicting a binary outcome (e.g. customer will or won’t default within six months). The most accurate gini value (we’ll call it the ‘full’ gini) is derived from the entire range of model predictions, but sometimes you are given a value calculated from a confusion matrix, a single partition of the data. Due to the method of calculation, this value (we’ll call it the ‘point’ gini) will be strictly less than the full gini value. Using simulations, we show that the difference between the point gini and the full gini is predictable.
Background
A friend brought to my attention a webpage where a company gave some details about the efficacy of their solution, a financial model built to identify loan defaults. From this webpage, we had just enough detail to generate the model’s confusion matrix, which led us to calculate a point gini value. Can this tell us anything about the value of the full, ‘proper’ gini?
Firstly, some quick definitions (and an excuse to show off pretty diagrams I’ve made…)
Confusion matrix
Anyone doing data science is probably very familiar with a confusion matrix, they look like this:
(See: Sensitivity and specificity: Confusion matrix for a more in-depth look)
In a nutshell, they are a crosstab of “what our model predicts” versus “what really happened”.
From the matrix we can calculate three important values: accuracy, the proportion of cases that are correctly classified by the model; and sensitivity / specificity, the proportions of actually positive/negative cases respectively that the model identifies correctly.
If a model is perfect, then the accuracy, sensitivity and specificity values are all equal to 1. If the model is no better than random guessing, the values are 0.5. If the model is perfectly bad (i.e. it predicts incorrectly every time), then the values are zero. Depending on our use case, the sensitivity and specificity values may not have equal weight for us, we may not care so much about the value of one as much as the other.
Important: we have to be clear about what we mean by ‘yes/no’, ‘accept/reject’. Often in credit risk, models are built with a negative status (e.g. ‘defaults within six months’) being the thing we’re trying to predict.
Now, underlying the model predicting ‘yes’ or ‘no’ (or ‘accept’, ‘reject’) there is likely to be some score that has been selected as optimal. In terms of a credit risk accept/reject model, think of it as a credit score cut-off: e.g. all credit scores over or equal to 550 we’ll accept, and those under, we’ll reject; we’ve chosen 550 because it gives us the best results. In our scenario, we don’t know what the specificity and sensitivity are for scores of 549, 548, 547, … or 551, 552, 553, … If we knew those values for all the possible scores, then we could generate a ROC curve.
ROC curve
The ROC (Receiver operating characteristic) curve is simply the True Positive Rate (aka sensitivity) plotted against the False Positive Rate (aka one minus the specificity), for every possible partition (by score).
The AUC (area under the curve) is the blue area as a proportion of the total area (which is 1), so an AUC of 1 implies a perfectly discriminating model. If the curve exactly matched the red dotted line, then that would be an AUC of 0.5, which means the model is no better than randomly guessing. The gini value is a scaled version of the AUC, calculated as gini = 2 * AUC – 1 — i.e. a gini of 1 is a perfect model, and a gini of 0 is no better than random guessing.
But in our scenario, we don’t have the full ROC curve; from the confusion matrix, we only have a single specificity value, and a single sensitivity value — hence, a single point on the ROC curve. The diagram looks like this:
(where the two triangles meet the blue curve, that’s our single point)
Therefore, the only AUC value we can calculate is the sum of the areas of the two shaded triangles. We can still compute the gini value, but as should be obvious from the diagram, it’ll always be an underestimate (we’re missing the two blue areas above the triangles). The whole point of this exercise is to find out how much of an underestimate.
Here’s a stack post that goes into some more detail: What is the formula to calculate the area under the ROC curve from a contingency table?, the top-rated answer is here. It’s a good, clear answer, I particularly like this bit: “You can technically calculate a ROC AUC for a binary classifier from the confusion matrix. But just in case I wasn’t clear, let me repeat one last time: DON’T DO IT!” Wise words.
(I do like this xkcd-style diagram of a ROC curve)
Annoying Aside: There’s more than one gini
To make things harder for everyone, there are multiple different statistics with the name ‘gini’ (sometimes capitalised, sometimes not):
Gini Inequality Index
The Gini inequality index, or sometimes just the Gini index, is a number that shows how evenly things are shared in a group, like income or wealth. In terms of wealth, a Gini index of 0 means everyone has exactly the same amount of wealth, i.e. total equality. A Gini index of 1 (or 100%) means one person has everything, and everyone else has nothing, i.e. total inequality. This value is often used by economists to compare wealth or income distributions between e.g. countries.
Gini Impurity
Gini impurity is used in machine learning, particularly decision trees; it’s used to measure how ‘mixed’ or ‘impure’ a group of items is, which is then used to decide whether to split a node or not. See Gini Impurity, A Simple Explanation of Gini Impurity and Decision tree learning : Gini impurity.
Gini Coefficient
This is the one we use most often in credit risk, and we’ll stick with the definition from above, i.e. it’s a scaled version of the AUC. In reality, there are different ways we can calculate it, which can result in slightly different values, but that’s for another day… NB: Sometimes the Gini inequality index is called the Gini coefficient; context is key.
There is no direct relationship between any of these three different values. See Gini coefficient for more details, but not necessarily more clarity(!)
The simulation
From here onwards, we do make an obvious assumption: for the presented confusion matrix data, the company chose a cut-off from their model that was ‘the best’, i.e. gave the most efficient (or profitable?) split in terms of good/bad prediction. To choose a cut-off that gives sub-optimal performance would make no sense.
Note: In our situation, we do have prior knowledge that the company’s model produces an underlying score distribution, and they’ve chosen a threshold in order to present their data.
The method behind the simulation is very straightforward: generate lots of random data, calculate our two ginis of interest, then see how they relate to each other.
The code
The first thing I needed was a way to generate a simulated dataset. How can we generate a random dataset with two variables, a Normal and a Bernoulli, with a defined correlation? Luckily, I found this stack post:
Generate a Gaussian and a binary random variables with predefined correlation, and the top answer gave the maths/stats formulas required. Rather than do the work myself, I took a screenshot and pasted the image into Claude, asked for an R function, and used that! Here’s the code it gave me:
generate_correlated_data <- function(n, p, rho) {
# Define the quantile for the standard normal distribution
x0 <- qnorm(1 - p)
# Define the density function for the standard normal distribution at x0
phi_x0 <- dnorm(x0)
# Calculate the correlation adjustment parameter r
r <- rho * sqrt(p * (1 - p)) / phi_x0
# Generate independent standard normal variables
X <- rnorm(n)
Z <- rnorm(n)
# Create the correlated variable Y
Y <- r * X + sqrt(1 - r^2) * Z
# Create the binary variable B
B x0)
# Return the dataset as a data frame
data.frame(B = B, Y = Y)
}
where n is the number of cases (rows), p is the probability of a 1 (e.g. a loan default), and rho is the correlation between the two variables. [Yes, I did test the code before I used it!]
I'm not going to present all my R code here -- it's fairly obvious stuff -- but the steps are:
0. Start with an empty results dataset
1. Choose p ~ U(0.05,0.5) and rho ~ U(0.1,0.8)
2. Using these values, generate a dataset of 1000 rows
with 'score' (integer) and 'bad' (binary) vars
3. Calculate the full gini
4. Generate the confusion matrices for every unique threshold (score)
5. For every confusion matrix, calculate the 'point' gini
6. Calculate the maximum 'point' gini
7. Add the full and point ginis to the results dataset
Repeat steps 1 to 7 1000 times.
And so we have our final dataset. As you might expect, there was a big correlation (0.93) between the rho chosen for the simulated dataset, and the full gini; but between the p and the full gini, no notable correlation (-0.07).
The plot below shows the 'best' (maximum) confusion matrix gini from each simulated dataset, versus the actual (full) gini value.
The pink dots are the samples, the blue line is a quadratic best-fit. As you can see, it has good agreement until around 0.9 on the x-axis. The formula for the line is:
So using this formula, if our best point gini is 0.5, then we might expect our full gini to be around 0.647.
Wrap-up
This was just a quick-and-dirty exercise to give us some confidence about a model where we had limited information. Obviously, it's always better to know the full gini.
If we wanted, we could increase the number of cases in our simulation, and generate confidence intervals.
Yet again, OpenAI's ChatGPT and Anthropic's Claude saved me quite a bit of time -- firstly writing the code to generate the correlated data, and also writing the LaTeX for the ROC curve diagrams (utilising the TikZ package). They're not perfect, but in my experience they get 95% of everything right, you just have to be on the look-out for silly errors and hallucinations.
Bonus function!
Additionally, we had a situation where we knew the sensitivity, specificity, accuracy and the total number of cases, but we wanted to know the original cell counts in the confusion matrix. As it's just 4 unknowns and 4 equations, it was easy to solve. Here's an R function to do the calculations:
counts_from_confusion_matrix_stats = function(sens, spec, acc, N) { stopifnot( sens != spec ); tp = N * sens * (acc - spec) / (sens - spec); fn = N * (sens - 1) * (spec - acc) / (sens - spec); fp = N * (spec - 1) * (acc - sens) / (sens - spec); tn = N * spec * (sens - acc) / (sens - spec); return(c(tp = tp, fn = fn, fp = fp, tn = tn)); }# Couple of examples:counts_from_confusion_matrix_stats(0.8,2/3,72/99,275);tp fn fp tn 100 25 50 100counts_from_confusion_matrix_stats(8/9,1/3,0.75,300);tp fn fp tn 200 25 50 25
Note that we check if the specificity and sensitivity are equal -- if they are, we can't continue, because there's no unique solution. (Also in this situation, the accuracy is necessarily equal to the same value.)
The Normal Distribution: R Functions
R has several built-in functions for working with the Normal (Gaussian) distribution, and I struggle to remember which one is which — do I want pnorm or dnorm? Frustrated by my own terrible memory, I put together a document that I can leave on my desktop and refer to whenever I like. I hope someone might find it useful!
The Normal Distribution: R Functions (PDF, 358KB)
Your Code Is My Data
Thirty second summary
Every decision made using data should be (a) recorded, and (b) replicable. For analytical and auditing purposes, it can be hugely beneficial to keep not only the results of the decision within the database, but also the decision mechanism (which would usually reside in the application codebase), and all the component parts of the calculation.
I have an (amicable) long-running disagreement with a developer friend about where business logic should live. He says it should live in the application code; I say it should live in the database as data. (Well, we would say that, wouldn’t we?) Of course, there are some caveats to my position; anyone could come up with situations in which it doesn’t work. All coding philosophies meet reality at some point.
(What even is the definition of ‘business logic’? I can’t see everyone agreeing on that!)
But sometimes, keeping the logic alongside the input data is demonstrably a good idea. Let’s explain with a practical example:
Customer assessment
Here’s our (vastly simplified) scenario: we can only lend to a customer if they (a) are over 25, (b) are a homeowner, (c) earn over £30,000 per year. We take a prospective customer’s data, check it against these thresholds, then they either pass or fail. Our database tables might look like this:
CREATE TABLE dbo.CustomerData ( CustomerID INT NOT NULL PRIMARY KEY ,Age TINYINT NOT NULL ,IsHomeowner BIT NOT NULL ,Salary MONEY NOT NULL ,AppDate DATETIME NOT NULL-- application date/time) CREATE TABLE dbo.LendingResult ( CustomerID INT NOT NULL PRIMARY KEY ,Pass BIT NOT NULL )
(ignoring otherwise crucial fields like a created date, named constraints, foreign keys etc)
So our C# developer’s code does the following:
- Gets the customer record from CustomerData
- Compares the individual fields to the thresholds
- Works out the overall result
- Inserts Pass = 0 or 1 to the LendingResult table
Any seasoned developer probably wouldn’t ‘bake’ the thresholds into their code, they’d be in a configuration file. Even better if the thresholds were taken from the database when the code was run and kept in memory (and periodically checked for changes).
Here’s the issue: my boss comes to me (as an analyst) and says we need to lend more — if we secured a funding line with fewer restrictions, what would be the effect of relaxing our thresholds? From the data we have in the database, how do I do that?
Using the set-up above, I potentially have to dig around in the codebase (or more likely, get someone else to do it for me), and create my own table of thresholds, e.g.:
CREATE TABLE dbo.Thresholds
(
ValidFrom DATETIME NOT NULL PRIMARY KEY
,Age TINYINT NOT NULL
,MustBeHomeowner BIT NOT NULL -- 0 = no, 1 = yes
,Salary MONEY NOT NULL
)
INSERT dbo.Thresholds(ValidFrom,Age,MustBeHomeowner,Salary)
VALUES
('2015-01-01 00:00:00', 30, 1, 40000)
,('2015-07-06 11:22:34', 30, 1, 35000)
,('2016-02-03 05:06:07', 27, 1, 34000)
,('2018-10-12 15:16:21', 26, 1, 32000)
,('2020-01-01 09:00:00', 25, 1, 30000)
GO
Then to look at previous decisions, I’d write a query like this:
SELECT cd.CustomerID ,cd.Age ,cd.IsHomeowner ,cd.Salary ,Historic_Decision = lr.Pass ,Current_Decision = CASE WHEN cd.Age >= t.Age AND cd.IsHomeowner = t.MustBeHomeowner AND cd.Salary >= t.Salary THEN 1 ELSE 0 END ,AgeDiff = cd.Age - t.Age-- other diff columns hereFROM dbo.CustomerData cd JOIN dbo.LendingResult lr ON lr.CustomerID = cd.CustomerID OUTER APPLY ( SELECT TOP 1 th.* FROM dbo.Thresholds th WHERE th.ValidFrom < cd.AppDate ORDER BY th.ValidFrom DESC ) t GO
Using this sort of query, I can analyse the differences between salaries, ages, and homeownership, and work out the effect of moving thresholds up or down.
But:
- What if the old thresholds aren’t available? “Sorry, we haven’t got that old code any more.”
- What if the calculation was more complicated than ‘must pass three thresholds’, and changed frequently over time?
- What if the ‘Pass’ value in LendingResult doesn’t match either the thresholds, or the data we hold for the customer?
These aren’t theoretical; I have faced all three of the above issues, multiple times.
Solution using XML/XSLT
In a recent post, Applying an XSLT to XML in SQL Server, I wrote about using XSLT as the basis of a scorecard engine that I’d built, that ran entirely within SQL Server. Although it was only ever used for the scorecard, I had plans to use the code elsewhere, wherever non-trivial decisions were made. The above scenario is a good example of where I might have rolled it out.
By using XML, we can not only keep the threshold values in the database, but also the mechanism by which the customer data is compared to the thresholds, plus every individual part of the calculation.
Here’s a piece of customer XML, and the XSLT we’re going to apply to it, to perform our suitability assessment:
DECLARE @inputXML XML DECLARE @inputXSLT XML SET @inputXML =' <customerData> <Age>20</Age> <IsHomeowner>1</IsHomeowner> <Salary>50000</Salary> </customerData> 'SET @inputXSLT ='<?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="/"> <xsl:variable name="Threshold_Age" select="30" /> <xsl:variable name="Threshold_MustBeHomeowner" select="1" /> <xsl:variable name="Threshold_Salary" select="30000" /> <xsl:variable name="Input_Age" select="customerData/Age" /> <xsl:variable name="Input_IsHomeowner" select="customerData/IsHomeowner" /> <xsl:variable name="Input_Salary" select="customerData/Salary" /> <xsl:variable name="Pass_Age"> <xsl:choose> <xsl:when test="$Input_Age >= $Threshold_Age"><xsl:value-of select="''Pass''" /></xsl:when> <xsl:otherwise><xsl:value-of select="''Fail''" /></xsl:otherwise> </xsl:choose> </xsl:variable> <xsl:variable name="Pass_Homeowner"> <xsl:choose> <xsl:when test="$Input_IsHomeowner >= $Threshold_MustBeHomeowner"><xsl:value-of select="''Pass''" /></xsl:when> <xsl:otherwise><xsl:value-of select="''Fail''" /></xsl:otherwise> </xsl:choose> </xsl:variable> <xsl:variable name="Pass_Salary"> <xsl:choose> <xsl:when test="$Input_Salary >= $Threshold_Salary"><xsl:value-of select="''Pass''" /></xsl:when> <xsl:otherwise><xsl:value-of select="''Fail''" /></xsl:otherwise> </xsl:choose> </xsl:variable> <xsl:variable name="Pass_Overall"> <xsl:choose> <xsl:when test="$Pass_Age = ''Pass'' and $Pass_Homeowner = ''Pass'' and $Pass_Salary = ''Pass''"><xsl:value-of select="''Pass''" /></xsl:when> <xsl:otherwise><xsl:value-of select="''Fail''" /></xsl:otherwise> </xsl:choose> </xsl:variable> <decision> <vars> <Age><xsl:value-of select="$Pass_Age" /></Age> <IsHomeowner><xsl:value-of select="$Pass_Homeowner" /></IsHomeowner> <Salary><xsl:value-of select="$Pass_Salary" /></Salary> </vars> <overall><xsl:value-of select="$Pass_Overall" /></overall> </decision> </xsl:template> </xsl:stylesheet>'DECLARE @resultXML XML SET @resultXML = dbo.ApplyXSLT(@inputXML, @inputXSLT) SELECT @resultXML AS resultXML GO
Running this code in SQL Server returns:
<decision>
<vars>
<Age>Fail</Age>
<IsHomeowner>Pass</IsHomeowner>
<Salary>Pass</Salary>
</vars>
<overall>Fail</overall>
</decision>
For clarity, I’ve chosen not to replicate the customer data and the thresholds in the above output XML, but in a real live system, I would absolutely do that.
Now, I know what some of you are going to say: that’s a lot of code to replace a 5-line CASE statement. But it took about 3 minutes to write, most of it is copy and paste.
We can wrap the above code into a function called fn_CheckThresholds (not defined here; it’s basically the same code as above, written as a standard T-SQL function). If we want to assess more than one customer, we can write a query like the following:
;WITH cte_CustomerData AS ( SELECT x.CustomerID ,x.Age ,x.IsHomeowner ,x.Salary FROM ( VALUES(1,20,1,50000) ,(2,30,0,31000) ,(3,30,1,29000) ,(4,30,1,31000) ) x(CustomerID,Age,IsHomeowner,Salary) ), cte_MakeXML AS ( SELECT cd.*, inputXML = ( SELECT c.Age ,c.IsHomeowner ,c.Salary FROM cte_CustomerData c WHERE c.CustomerID = cd.CustomerID FOR XML PATH('customerData'), TYPE ) FROM cte_CustomerData cd ), cte_Check AS ( SELECT m.* ,Result =dbo.fn_CheckThresholds(m.inputXML)FROM cte_MakeXML m ) SELECT CustomerID ,Age ,IsHomeowner ,Salary ,Result_Age = Result.value('(decision/vars/Age/text())[1]','VARCHAR(5)') ,Result_IsHomeowner = Result.value('(decision/vars/IsHomeowner/text())[1]','VARCHAR(5)') ,Result_Salary = Result.value('(decision/vars/Salary/text())[1]','VARCHAR(5)') ,Result_Overall = Result.value('(decision/overall/text())[1]','VARCHAR(5)') FROM cte_Check GO
which returns:
(In an actual system, the XSLT would live in its own table, where the latest row would be the current version.)
With the code structured like this, I can run my query whenever and however I want — and just to emphasise the point, none of this is a copy of the data used in the decision-making, it is the data and the decision-making mechanism, all wrapped up together! And because XSLT is just XML, I can query that too, if I want to.
[Oh, and of course, XML/XSLT can contain comments — comment everything!]
And finally
As a database developer and analyst, I spend a fair bit of time chasing down what columns in database tables actually mean, and where the data comes from in the first place. Due to developer churn, it’s not at all uncommon to find corners of the codebase / database that no-one knows anything about: “We don’t touch that, Jim wrote it”, and Jim left 5 years ago. This is obviously bad news, well-functioning businesses shouldn’t be in this position. Moving logic into the database can prevent important decisions being hidden away, and can additionally help shorten the path between ‘data’ and ‘actionable information’.
I know that XML and XSLT scares some people off, but it’s enormously powerful (and well-supported). The example above is a toy one to demonstrate the process — but in a real-world scenario, you may have dozens of variables, and a more complex decision tree. Without an organised way of handling it, silly mistakes are going to happen.
Sure, you may not want to be processing XSLTs inside SQL Server for administrative or security reasons; I’ve never had any issues, but for busy production servers, keeping them as ‘locked-down’ as possible is desirable. But delegating the 'get data as XML / get XSLT / process XML,XSLT / store XML result' process chain to a separate service should be trivial these days.
Converting the incomplete gamma function
I’m working on a piece of code that requires the generation of pairs of correlated random variables. Ultimately, the code will be run in SQL Server (which doesn’t natively have any libraries of mathematical or statistical functions like R and Python do) , so I’m having to dig out algorithms for some fairly fundamental functions. Two of those are the log gamma function, and the incomplete gamma function.
My go-to for all these sorts of algorithms is the well-respected Numerical Recipes book. (I own a physical copy of the 2nd edition, where the code is in C, and a digital copy of the 3rd edition, where the code is in C++.) Once I have the code working in R, then I can easily convert it to T-SQL.
Log gamma
The incomplete gamma function requires the log gamma function; here’s the Numerical Recipes’ version, gammln, converted from C to R:
gammln <- function(xx) {
cof <- c(
76.18009172947146, -86.50532032941677, 24.01409824083091,
-1.231739572450155, 0.1208650973866179e-2, -0.5395239384953e-5
)
x <- xx
y <- x
tmp <- x + 5.5
tmp <- tmp - (x + 0.5) * log(tmp)
ser <- 1.000000000190015
for (j in 1:6) {
y <- y + 1
ser <- ser + cof[j] / y
}
return(-tmp + log(2.5066282746310005 * ser / x))
}
(If you want to know how the algorithm works: Lanczos approximation)
Because the code is in R, we can easily compare the output of this function to the output of the native R function calls:
tst_x = seq(0.01,100.00,by=0.01); length(tst_x); # 10,000 res = sapply(tst_x, function(x) { c(x,gammln(x),log(gamma(x))) }) resdf = as.data.frame(t(res)); colnames(resdf) = c("x","v1","v2"); # v1 = gammln , v2 = native fn calls summary(resdf$v1 - resdf$v2);Min. 1st Qu. Median Mean 3rd Qu. Max. -6.130e-15 1.802e-11 5.772e-11 5.280e-11 8.550e-11 1.043e-10
So for values between 0.01 and 100, the largest discrepancy between the two was 0.0000000001043 — pretty accurate, I think you’ll agree.
Incomplete gamma
First, some terminology to clear up. Section 6.2 in Numerical Recipes defines two complementary incomplete gamma functions, P and Q, as:
where a > 0. However, in the Wikipedia entry for Incomplete gamma function, the upper incomplete gamma function is defined as:
and the lower incomplete gamma function is defined as:
In other words:
and
Same/similar names for functions that are scaled versions of each other — I actually spent 10 minutes debugging the code before I realised why my test values were different from those given by Wolfram Alpha!
In Numerical Recipes, P(a,x) and Q(a,x) are named gammp and gammq, respectively. These functions call two other functions, gser and gcf.
EPS <- 3.0e-7
FPMIN <- 1.0e-30
gser <- function(a, x) {
gln <- gammln(a)
ap <- a
del <- sum <- 1.0 / a
repeat {
ap <- ap + 1
del <- del * (x / ap)
sum <- sum + del
if (abs(del) < abs(sum) * EPS) {
return(sum * exp(-x + a * log(x) - gln))
}
}
}
gcf <- function(a, x) {
gln <- gammln(a)
b <- x + 1.0 - a
c <- 1.0 / FPMIN
d <- 1.0 / b
h <- d
i <- 1
repeat {
an <- -i * (i - a)
b <- b + 2.0
d <- an * d + b
if (abs(d) < FPMIN) d <- FPMIN
c <- b + an / c
if (abs(c) < FPMIN) c <- FPMIN
d <- 1.0 / d
del <- d * c
h <- h * del
if (abs(del - 1.0) <= EPS) break
i <- i + 1
}
return(exp(-x + a * log(x) - gln) * h)
}
There are two versions for reasons of accuracy: gser uses the series representation, gcf uses the continued fraction representation. (See the book for more detail.)
gammp and gammq are defined like this:
gammp <- function(a, x) {
if (x < (a + 1.0)) {
gamser <- gser(a, x)
return(gamser)
} else {
gammcf <- gcf(a, x)
return(1.0 - gammcf)
}
}
gammq <- function(a, x) {
if (x < (a + 1.0)) {
gamser <- gser(a, x)
return(1.0 - gamser)
} else {
gammcf <- gcf(a, x)
return(gammcf)
}
}
As before, we can check the output of these functions against existing versions. There are several in different libraries, e.g. expint::gammainc and pracma::gammainc. (NB: Annoyingly, the parameters aren’t always given in the same order: expint::gammainc is called with (a,x), whereas pracma::gammainc is called with (x,a).)
Let’s generate a few thousand values at random:
Z = 10000;
res = matrix(rep(0,6*Z), ncol=6,byrow=T);
for(i in 1:Z)
{
a = runif(1, min=0,max=10);
x = runif(1, min=0,max=10);
res[i,] = c(i, a, x, gammp(a,x), gammq(a,x), expint::gammainc(a,x));
}
resdf = as.data.frame(res); colnames(resdf) = c("i","a","x","P","Q","g");
We’ll look at the relative error between the two methods, and then check that P+Q equals one, as defined:
summary(((resdf$Q*gamma(resdf$a))-resdf$g) / resdf$g)Min. Max. -3.8845752915850290e-07 ... 5.3260205117148226e-07summary(resdf$P+resdf$Q);Min. 1st Qu. Median Mean 3rd Qu. Max. 1 1 1 1 1 1
The maximum relative error is approx. 5e-07, and the two versions of the Numerical Recipes functions add up to 1 everywhere. All good!
The reveal…
There’s something I’ve glossed over on purpose — how did I convert the C/C++ code into R? Sure, I could’ve done it manually, but that’s so 2023. Actually, I took photos of the code in the Numerical Recipes book, uploaded the photos to ChatGPT, and using the GTP-4o model, asked it to convert the code to R — which it did, first time. And the code worked — also first time.
In fact, in a future post where I demonstrate how I used these incomplete gamma functions, I’ll show how GPT-4o used a screenshot (from a Stack Overflow post) of the algebra that underpins what I’m trying to achieve, and wrote the R code for that too! LLMs are saving me hours of work, and turning previously impractical projects into practical reality.
Whole Lotta History: Old data and SQL
Posted by sqlpete in architecture, coding, systems on June 19, 2024
Thirty second summary
In databases, the facility to see data as it looked at a specified time in the past is something that often gets added as an afterthought. We talk about four main approaches here, taking into account that certain data needs to be thought of as existing as part of a ‘set’, e.g. customer addresses. The identification of these sets is usually trivial by eye, but can be harder to identify programmatically.
I apologise in advance for the number of times I use the word ‘blob’ later on in this post. (I did ask Copilot for better suggestions, but it failed me.)
Old data
A company keeps data about their customers for the life of their dealings with them (and maybe beyond, see How long can you keep personal data under UK GDPR?). There are situations when you need to know exactly how their data looked at a specified point in the past, even though that data might be irrelevant now.
So here’s our scenario: we have a database, with tables, and data. That data changes over time (some of it very frequently). Without using any external system, we need to keep track of all those changes, such that we can rewind to any point in time and see what the data was then.
What’s the best way of doing it?
There are two requirements that we want to be true of any solution: (1) the developers can do their usual CRUD, without having to think about versioning or historics (a word commonly used to refer to old data); and (2) the BI department must be able to get at whatever cut of the data they need, as painlessly as possible.
There are two ways we can go about it: (1) use SQL Server’s built-in mechanisms, such as triggers — and now temporal tables; or (2) create our own set-up, such that storing old data is an inherent part of the schema.
The added wrinkle: Sets
As well as solving the problem of keeping historics, I want to add another thing into the mix: some of the concepts we care about, I think of as sets, logical groupings of contemporaneous data. A good example is addresses: at any point in our dealings with a customer, we want to know what all their relevant addresses were. Where did they live at that date? (They can have more than one residence, e.g. students). Where did they live before? Did they tell us about an address that they subsequently changed or deleted? What was the primary address for correspondence? (If they’re students, do they need more than one primary address?) We’ll use addresses as our main example from hereon (but we’ll keep the address data to one simple string, rather than have multiple address lines, postcode, etc.).
The concept of set membership can be defined into the database schema explicitly (using standard ‘normalised’ tables), but more often than not, it’s defined by time boundaries: each time a customer address is changed or added, that denotes a new set. If several address records are updated within a short time, we might want to group these together and consider them as one distinct update.
In my experience, there are four main approaches to storing this data, which I’ll go through below. To an extent, the approaches can be mixed, depending on the complexity of the data (and the tolerance of the developers to working with these structures).
0. Don’t keep any historics!
The default approach: don’t do anything. I can’t tell you how many times I’ve been asked for data on, for example, “What did the customer put down as their place of work prior to the application being approved?”, only to find that historics hadn’t even been considered, and that it’ll take a new ticket to start logging the changes (usually by doing the bare minimum: see the next section).
For the people at the back, let’s use bold because it’s important: KEEPING HISTORICS IN A CRUMMY FORMAT IS BETTER THAN NOT HAVING ANY HISTORICS AT ALL, EVEN IF YOU’RE JUST LOGGING IT TO A TEXT FILE (yes, this happens, but obviously don’t do that). If the data is somewhere, us resourceful data people can usually recreate the historical data. But you can’t recreate history from no data at all.
[You don’t need to worry about historics if the data never changes, or changes so infrequently it’s not worth the overhead; e.g. lookup tables of fundamental business concepts.]
1. Audit tables
This is by far the most common solution I come across, because it’s easy to implement. You use triggers to record a copy of the old data in a separate table (quite often the same name as the original table with the suffix ‘Audit’), whenever data is updated or deleted.
Below is the SQL to create an Address table, an AddressAudit table, and a trigger to copy the data from one table to the other:
CREATE TABLE dbo.[Address]
(
AddressID INT NOT NULL PRIMARY KEY CLUSTERED
,CustomerID INT NOT NULL
,[AddressStr] VARCHAR(255) NOT NULL
,CreatedOn DATETIME NOT NULL DEFAULT(GETDATE())
)
GO
CREATE TABLE dbo.AddressAudit
(
AddressAuditID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED
,AddressID INT NOT NULL
,CustomerID INT NOT NULL
,[AddressStr] VARCHAR(255) NOT NULL
,CreatedOn DATETIME NOT NULL DEFAULT(GETDATE())
,AuditType CHAR(1) NOT NULL
,AuditDate DATETIME NOT NULL DEFAULT(GETDATE())
)
GO
CREATE TRIGGER dbo.tr_Address_Audit
ON dbo.[Address]
AFTER DELETE,UPDATE
AS
BEGIN
SET NOCOUNT ON
DECLARE @AuditType CHAR(1)
IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
SET @AuditType = 'U'
ELSE IF EXISTS(SELECT * FROM inserted)
SET @AuditType = 'I'
ElSE IF EXISTS(SELECT * FROM deleted)
SET @AuditType = 'D'
INSERT dbo.AddressAudit
(
AddressID
,CustomerID
,[AddressStr]
,CreatedOn
,AuditType
)
SELECT
AddressID
,CustomerID
,[AddressStr]
,CreatedOn
,@AuditType
FROM DELETED
END
GO
Note that I’ve only declared the trigger to fire on DELETE and UPDATE, not INSERT. This is because we don’t actually need INSERT; we can recover the original data from the first UPDATE, because we’re saving the old data into AddressAudit. (We know an UPDATE has happened if the same ID exists in both the special tables INSERTED and DELETED: new data in INSERTED, old in DELETED).
We’ll insert/update/delete some addresses:
INSERT dbo.[Address](AddressID,CustomerID,[AddressStr])
VALUES(1, 101, '123 The High Street, Luton')
INSERT dbo.[Address](AddressID,CustomerID,[AddressStr])
VALUES(2, 101, '345 Nice Lane, Dunstable')
UPDATE dbo.[Address]
SET [AddressStr] = '123a The High Street, Luton'
WHERE AddressID = 1
DELETE FROM dbo.[Address] WHERE AddressID = 1
Let’s see what data our tables contain now:
SELECT * FROM dbo.[Address]
SELECT * FROM dbo.AddressAudit
So far, so expected. This data tells us that this Customer had four distinct sets of address data:
- ‘123 The High Street, Luton’
- ‘123 The High Street, Luton’, ‘345 Nice Lane, Dunstable’
- ‘123a The High Street, Luton’, ‘345 Nice Lane, Dunstable’
- ‘345 Nice Lane, Dunstable’
The problem now: How do we turn the data in our tables into the sets above? What we want is a recordset that looks like this:
(where the AuditType of ‘X’ means the record is carried over from the previous set without any changes)
Honestly, the code to reproduce the above recordset from the two source tables is rather fiddly and longer than you might expect (I keep a template handy in my ‘folder of useful scripts’). But, the crucial point is that it is possible to fully recreate the history we’re looking for.
Note that the AuditDate in the audit table gives you the date that the accompanying row data was invalidated — to get the date from which the data was current, you need to use LEAD/LAG in your query. It gets more complex if you want to treat changes that happened within a short timeframe as one big change; you’ll have to employ Itzik Ben-Gan’s ‘gaps and islands’ code, which I wrote about here: Inexact date grouping using islands
This approach is how less-experienced devs would deal with storing historical data; usually the audit tables and triggers are slapped on as an afterthought (and they then apply this pattern everywhere). There’s nothing wrong with this way of doing it, but it’s not particularly friendly to work with — you end up writing a lot of SQL to get back to the data you actually want.
And it’s worth mentioning: in the data above, AddressIDs 1 and 2 always refer to the same two physical properties. But there’s nothing stopping a user updating the AddressStr to a completely different property. You’d have to guard against that somehow: usually this happens in the input form, but it’s wise to check for this in the database too.
2. Temporal tables
Introduced with SQL Server 2016, Temporal tables are basically an ‘official’ (and nicer) replacement for the ‘audit table + trigger’ solution above.
I’ll get Copilot to do some work, what does it have to say about temporal tables?
Temporal tables, also known as system-versioned temporal tables, are a feature in SQL Server that provides built-in support for tracking changes in data over time. These tables maintain a full history of data changes, allowing easy point-in-time analysis. Every temporal table has two explicitly defined columns, each with a datetime2 data type, referred to as period columns, which are used by the system to record the period of validity for each row. In addition to these period columns, a temporal table also contains a reference to another table with a mirrored schema, called the history table. The system uses the history table to automatically store the previous version of the row each time a row in the temporal table gets updated or deleted. This feature is useful for auditing, rebuilding data in case of inadvertent changes, projecting and reporting for historical trend analysis, and protecting data in case of accidental data loss.
SQL Spreads has an article worth reading: Temporal Tables and how to use them in SQL Server
Here’s the DDL for our Address table, using a temporal table to store the historics:
CREATE TABLE dbo.[Address] ( AddressID INT NOT NULL PRIMARY KEY CLUSTERED ,CustomerID INT NOT NULL ,[AddressStr] VARCHAR(255) NOT NULL ,CreatedOn DATETIME NOT NULL DEFAULT(GETDATE())-- Columns required for temporal table:,SystemValidFrom DATETIME2 GENERATED ALWAYS AS ROW START ,SystemValidTo DATETIME2 GENERATED ALWAYS AS ROW END ,PERIOD FOR SYSTEM_TIME(SystemValidFrom, SystemValidTo) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.AddressHistory)) GO
(Note that we don’t have to create the table AddressHistory ourselves, SQL Server does it for us.)
If we do the exact same INSERTs, UPDATE and DELETE as in the previous section, our table data looks like this:
i.e. very similar to the tables in the previous section. But look at the CreatedOn and System dates, they’re an hour apart, what’s happened? Annoyingly, “System-versioned temporal tables store values for period columns in UTC time zone” (from Temporal table usage scenarios, which you should definitely read before using Temporal Tables) — and as far as I can tell it can’t be changed, so you have to adjust for this in your queries.
So how do we get our ‘sets’ of Address data back? We can see there are four different System dates, so we could do this:
SELECT SetID = 1, * FROM [Address] FOR SYSTEM_TIME AS OF '2024-06-19 11:01:31.5259598'
UNION ALL
SELECT SetID = 2, * FROM [Address] FOR SYSTEM_TIME AS OF '2024-06-19 11:01:31.5309870'
UNION ALL
SELECT SetID = 3, * FROM [Address] FOR SYSTEM_TIME AS OF '2024-06-19 11:01:55.2601789'
UNION ALL
SELECT SetID = 4, * FROM [Address] FOR SYSTEM_TIME AS OF '2024-06-19 11:02:06.4848418'
ORDER BY SetID, AddressID
which returns:
which is exactly what we want. (Clearly, the ‘proper’ SQL will be smarter than the query above, but it demonstrates the point.)
3. ‘Roll-your-own’ versioning
Of course, instead of the above, you can start down the road to solving the problem using standard database architecture, and the first step might look something like this:
CREATE TABLE dbo.[Address]
(
AddressID INT NOT NULL
,[AddressStr] VARCHAR(255) NOT NULL
)
CREATE TABLE dbo.AddressSet
(
AddressSetID INT NOT NULL PRIMARY KEY
,CustomerID INT NOT NULL
,ValidFrom DATETIME NOT NULL
)
CREATE TABLE dbo.AddressSet_Address
(
AddressSetID INT NOT NULL
,AddressID INT NOT NULL
,PRIMARY KEY (AddressSetID, AddressID)
,FOREIGN KEY (AddressSetID) REFERENCES dbo.AddressSet(AddressSetID)
,FOREIGN KEY (AddressID) REFERENCES dbo.[Address](AddressID)
)
The above schema is a lot more basic that we’d need in reality. Note that we won’t be UPDATE-ing or DELETE-ing anything, we’re always INSERT-ing new data — there’ll be a new AddressSet record each time a change is made.
Note also that, as you’d expect, the CustomerID has moved from the Address level to the AddressSet level. To get the current addresses for a Customer, you pick the record with the newest ValidFrom date.
Something else to consider: there’s no real concept of a singular physical address; records in the Address table might all point to the same real-world place, but there’s nothing in the data to indicate it. For that, you’d need either (a) a canonical datapoint, like the UDPRN; or (b) an AddressVersion table — but the AddressSet/Address mapping table will then need to point to the AddressVersionID, which itself points to the Address record… and it all starts to get a little tricky. And we haven’t even tried to incorporate concepts like address type, primary address, or custom sort orders.
While on paper, this could look like the ‘proper’ way to store the data, you can easily find yourself going down a rabbit hole of normalisation, and unless you’re using sprocs for your CRUD, the developers get annoyed and stop responding to your emails!
4. Everything’s a blob
Similar to the above, we can keep track of the historics ourselves, but instead of splitting out any constituent parts, we’ll put the whole lot into a ‘blob’ of JSON or XML.
SQL Server has a native XML datatype, but currently you’ll have to store JSON as VARCHAR/NVARCHAR. [Late-breaking news: There’s a JSON datatype in Azure SQL!]
Every time an address gets added, modified or deleted, the blob changes, and we insert a new record into the table, which would resemble this:
CREATE TABLE dbo.AddressSet
(
AddressSetID INT NOT NULL PRIMARY KEY
,CustomerID INT NOT NULL
,AddressSetBlob XML NOT NULL
,ValidFrom DATETIME NOT NULL
)
(You could have only one blob ever per Customer, and keep track of the changes within the blob itself, but I can’t see any advantage in that.)
To get the current set of addresses for a Customer, we pick the record with the newest ValidFrom date.
Pros of this solution:
- It only needs one table! The database schema couldn’t be simpler.
- Some front-end devs actively prefer working with data this way.
- For a production system that requires blistering speed, this could be the best solution; your application system might even be a flavour of NoSQL already.
- It’s relatively simple to parse the data out to a more normal form in your reporting database — the determination of what forms a set of addresses is already done for you: one record == one set (modulo gaps/islands, as above).
Cons:
- Not great for live querying, e.g. ‘Find me all the customers who lived in London in 2021’. Worst case scenario, the engine would have to look at every record. (Hopefully, no-one would ever build a system with such a glaring deficiency).
- It’s greedy in terms of space: you change one tiny thing on one line, and you duplicate the entire set of addresses.
- Some front-end devs actively DO NOT prefer working with data this way!
- It’s not straightforward to put constraints on the data within the blob – you could use XSDs, or you’ll be relying on the input form to do this; or you have to do extra processing / sense-checking when the blob is INSERTed into the database.
As someone who loves working with XML within SQL Server, I always keep an eye out for when this could be the optimum solution.
Wrap-up
In my view, Solution 1 (triggering into an audit table) is primitive, but it does undeniably work — which is good, because I see it everywhere. Solution 2 (temporal tables) should be replacing solution 1 in most places. To be completely honest, I’ve not used temporal tables that much; I don’t know of any issues with them, but I look forward to testing the heck out of them. Solution 3 (normalised data): the geek in me appreciates the aesthetics, but I find it leads to regrets later on. Solution 4 (JSON/XML) is the only real solution for large, hierarchical data (e.g. credit files), and you may find it useful in other situations.
Of course, for all of the above, I’m focusing on storing historics in a production database, but we also have to think about how we’ll store the historics in a BI/reporting setting. XML/JSON might work perfectly in production, but in our reporting warehouse, we’ll need to normalise the data out into a more useful schema. Luckily, T-SQL has decent support for parsing XML, and to a slightly lesser extent, JSON.
As ever (and I make no apology for repeating myself): test! Use real data if you can — if not, take the time to generate fake data, and make it as ‘real-looking’ as possible. Fill your tables with tens of thousands of records. Then write the queries to get the data back out: what did customer X’s data look like on date Y?
How do you match address Z? Who lived in town Q between 2001 and 2003? etc.
Determining SQL datatypes from wide CSV data
Posted by sqlpete in architecture, coding on May 1, 2024
Thirty second summary
When importing a CSV file, SQL Server’s Import/Export wizard isn’t great at working out the correct column datatypes for the new table — which is especially painful when the CSV is wide (has many columns) — so I wrote some SQL code to get round the issue.
Everyone working in BI / data science has to move data around between apps all the time, and it’s a constant surprise to me how this is still somewhat of a pain. In particular, getting CSV data into a SQL Server table with the datatypes you require is a very hit-and-miss affair. The GUI tools SSMS provides you with aren’t really up to the task, especially when the CSV files are very wide.
In this post, I’ll demonstrate how I tackled this problem.
Motivation
Warning: This section is quite waffle-y, please feel free to skip
In 2018, the consumer lender Home Credit ran a competition on the data science community site kaggle, to see who could come up with the best predictive model for a given data set: “The objective of this competition is to use historical loan application data to predict whether or not an applicant will be able to repay a loan.” All the details are given here.
The competition closed 3 months later with a whopping 7180 entries! The winning team, comprising 6 senior data scientists, won $35,000. In discussion about their winning code, they wrote: “… for this kind of competition and this kind of problem, feature engineering and feature selection are still the most important step.”
Feature engineering means manipulating the data you have to generate more predictive variables, and it’s hands down my favourite part of building models — it’s basically playing with data, using as much creativity as you can muster. Some examples from my own work:
- Parsing business names and addresses to tease out company-level data that didn’t otherwise exist
- Sub-models of ‘customer enquiry’ data to add a much richer ‘search score’ (how ‘urgent’ the customer’s requirement for the money is)
- Combining socio-demographic statuses to paint clearer pictures of applicants
- Augmenting residential statuses with given electoral roll data to better explain a customer’s living situation
- Generating huge numbers of ratios concerned with debt, income, expenses, and all aspects of affordability
It’s then straightforward to weed out those variables that aren’t predictive (part of feature selection).
Having some time on my hands, I decided to play with this dataset, and see how close I could get to the performance of the winning teams. I’m not completely daft — I’m not expecting much, some (all?) of the winning team are PhD ‘Heads of Data Science’ at huge companies. Still, it seems like a fun thing to try. Stay tuned for a future post on my efforts!
SQL Is Best
Now, because I’ve used it, day in and day out, for so many years, I’m accustomed to getting to grips with my data, trying to really understand it, in SQL Server — not Python, or even R. So, I want to get the data into SQL Server first. And here’s the stumbling block: we have multiple CSV files, some of them with over a hundred columns.
Anyone who uses SSMS/SQL Server knows that the Import Wizard is… let’s be polite, ‘lacking in features’. Or less polite, bloody annoying. For me, a far more flexible and fruitful approach is to import all the data as text ([N]VARCHAR), and do all our transformations using pure SQL.
The first CSV I’m looking at (application_train.csv from the competition dataset) has 122 columns, and while I could work out the datatypes manually, it’s more fun to write code to do it for me!
My solution
Let’s get the data into SQL Server as text.
Getting the raw data into SQL Server: BULK INSERT
I copied/pivoted the column names from the first row of the CSV, and created a SQL table app_train_raw that consisted of one integer column (the unique customer ID), and 121 VARCHAR(50) columns. I then inserted the data using BULK INSERT:
BULK INSERT dbo.app_train_raw FROM '...\application_train.csv' WITH ( FORMAT = 'CSV' ,FIRSTROW = 2-- i.e. skip header row,ROWTERMINATOR = '0x0a'-- character 10, Line Feed)
It took a few seconds to insert the 307,511 rows, a total of 37,516,342 data points.
Warning: BULK INSERT doesn’t give good error messages. I kept getting an error about ‘conversion error (truncation)’, so spent ages messing with text encodings, trying to find rogue characters in the CSV — when in fact, I’d miscounted the number of columns!
The code
I’m not going to present the entirety of my code, because (a) this post would be too long, (b) it’s just ‘glue’ code. I maintain a config table with one row per column from the original CSV, and whether I’ve already determined the data type, or created a lookup. Also, I’m doing this all iteratively: identifying the types of columns as I go along, then re-running sections of code, ignoring those with known types.
From here on, I’ll post only the most relevant bits.
Initial categorisation
In my post Aggregating all the categorical data in a table, there’s code I wrote to, well, aggregate all the categorical data from a table (in order to calculate the Population Stability Index for each variable in the models).
We can use the exact same code here, and run it over all 121 VARCHAR(50) columns. You might think it would take a long time to GROUP all these columns, but it took just 19 seconds on my old PC. We’re left with the global temp table ##Results, containing 388,218 rows: each row is a distinct variable/value pair, we’re not interested in duplicate values.
Analysing the categories
The first thing we can do is look for obvious candidates for columns that we can ‘normalise out’ as lookup tables:
SELECT [var], COUNT(1)
FROM ##Results
GROUP BY [var]
HAVING COUNT(1) < 30
This led me to 13 columns (mostly ending _STATUS, _TYPE) that I turned in to lookup tables. This didn’t take long, and could easily be automated.
Next, let’s gather some information about the remaining columns (I’d already removed the 13 lookup columns from ##Results):
SELECT
[var]
,HasNULL = SIGN(SUM(IIF([value] IS NULL, 1, 0)))
,NumCats = COUNT(1)
,NumNumeric = SUM(ISNUMERIC([value]))
,MinVal = MIN([value])
,MaxVal = MAX([value])
,MinLen = MIN(LEN([value]))
,MaxLen = MAX(LEN([value]))
,NumWithDP = SUM(CASE WHEN CHARINDEX('.',[value]) > 0 THEN 1 ELSE 0 END)
,NumWithSN = SUM(CASE WHEN PATINDEX('%[0-9]e[+-][0-9]%',[value]) > 0 THEN 1 ELSE 0 END)
,NumWithNeg = SUM(CASE WHEN LEFT([value],1) = '-' THEN 1 ELSE 0 END)
FROM ##Results
GROUP BY [var]
The code is fairly self-explanatory: var is the column of interest, and we’re counting the number of distinct instances of values where the value is numeric, or contains a period (decimal point), or scientific notation (‘Xe-Y’), or a minus sign at the start. We also want to know the min/max alphabetic values (for identifying 0/1 or N/Y), and the min/max length of the values (for deciding between FLOAT and DECIMAL).
Using this result set, we can say:
- If there are only 2 categories (or 3 including NULL), and the min/max values are 0/1 or N/Y, then we’ll treat this column as a BIT
- If there are no non-numeric values, and any string contains scientific notation, or contains a decimal point and is longer than 18 characters, we’ll treat this as a FLOAT
- If there are no non-numeric values, and no decimal points, then we’ve either got a BIGINT, INT, SMALLINT or TINYINT (not forgetting that TINYINTs can’t be negative, so we check that the first character isn’t a minus sign
This takes care of over half the columns.
Numeric types
We’ve already determined some of the numeric types, but we need to investigate further. Mainly, we’re concerned with identifying the correct precision/scale parameters for DECIMAL types. Note that the first CTE only considers numeric columns we haven’t identified the correct types for yet.
;WITH cte_UnknownOnly AS ( SELECT r.[var] ,floatval = CAST(r.[value] AS FLOAT) ,lp = LEFT(r.[value], CHARINDEX('.', r.[value])-1) ,rp = SUBSTRING(r.[value], 1+CHARINDEX('.', r.[value]), 255) FROM ##Results r WHERE r.var IN(... {unknown numeric columns only}... )) SELECT r.[var] ,MinAsFLOAT = MIN(r.floatval) ,MaxAsFLOAT = MAX(r.floatval) ,MinLenLP = MIN(LEN(r.lp)) ,MaxLenLP = MAX(LEN(r.lp)) ,MinLenRP = MIN(LEN(r.rp)) ,MaxLenRP = MAX(LEN(r.rp)) ,NumDistRP = COUNT(DISTINCT r.rp) ,MinINTLP = MIN(CAST(r.lp AS INT)) ,MaxINTLP = MAX(CAST(r.lp AS INT)) ,MinINTRP = MIN(CAST(r.rp AS INT)) ,MaxINTRP = MAX(CAST(r.rp AS INT)) FROM cte_UnknownOnly r GROUP BY r.[var]
where LP refers to the piece on the left of the decimal point (the integer part), and RP is the piece on the right (the fractional part).
From this result set, it’s easy to work out the correct parameters for DECIMALs using MaxLenLP and MaxLenRP. NumDistRP is useful for deciding whether to simplify some types, e.g. salaries are usually given as whole numbers, but a few may contain fractional parts due to calculations, and you may wish to round the figures for simplicity.
This step took care of the rest of the columns — we now know the correct datatypes for the whole dataset.
Wrap-up
I hear you say: “This looks like a lot of work, I can eyeball the column types faster and just use the wizard.” Good luck to you, I say — I’ve spent far too many hours of my life looking at unhelpful error messages from the Import wizard. And what if you’ve got multiple CSVs with over a thousand columns in each? It’d take days to get all the types correct. This code gives me all the control over the datatypes I could want. (Consider also that I generate the INSERT/SELECT statements from the same config table; I’ve omitted the details here to save space.)
Could I ‘product-ise’ this to make it a single piece of code for anyone to run? Sort of, but not really. There are so many edge cases and ‘quirks’ to cater for. As an example here, the columns prefixed with ‘AMT_REQ_CREDIT_BUREAU’ are presented in the data as having one decimal place, but in reality, they’re all integers (as they’re counts of enquiries).
Note that if the CSV file is too big, you could restrict the categorisation to the first 1000 rows (say). This would probably be ok for well-behaved datasets… but when was the last time you got a well-behaved dataset to work on?
Finally, an anecdote: I once was involved in a project to continuously transfer data from one internal system (Microsoft Dynamics) to another (our SQL Server instance) — the ‘official’ Microsoft connector was unreliable, and flaked out several times a week. The company chose a third-party provider that had a product they claimed could transfer data between hundreds of different systems, and did exactly what we needed — or so we thought. Turns out, after we built our import layer, the product only transferred the data stripped of all formatting, i.e. we received only strings into the target system, whether the original data had been numbers, dates or text. Much swearing ensued! We couldn’t believe this was correct, and the provider was surprised that we were annoyed (“that’s how everyone does it” — is that true? Let me know if you think so…), and we then had to write a ton more code to convert the strings back to the datatypes we needed [which was where I first realised you could use SQL to work out the types]. The lesson I take away is: always check even your most utterly basic assumptions!
Diary Entry #1
If there’s one thing I hate, it’s not being busy. I can’t really switch off, I’m always thinking about tech I could be playing with. In that vein, here are a few of the diversions that have been occupying my time since my last post on here.
Warning: rambling ahead!
Poking My Nose Into Other People’s Business
As a boy, I drove my parents nuts by forever taking stuff apart. There wasn’t an electrical or electronic appliance in the house that I hadn’t had in pieces at some point, and I wasn’t the best at putting them back together. My bedroom floor was strewn with circuit boards, motors, lights, speakers, batteries and general metal junk. Because I’m a grown-up and can’t do that any more (bah), I like to pick apart other people’s code and algorithms, and see how they work.
Lending Calculators
A while ago, one of my projects at work was to figure out how the lending calculators for various car finance firms worked – or in a couple of cases, didn’t work.
Anyway, I found this a great deal of fun, so I considered starting a side blog detailing all my efforts in this area. However, I ran the idea past a friendly compliance person (Hi Debs!) who advised against it: big companies don’t like to be shown to be wrong, and have expensive lawyers to set on anyone who implies otherwise. (Yes, I could still do it if I anonymised the companies involved — I might still do that, but it’s extra work with screenshots, etc.)
[Aside: There was one particular online calculator, not affiliated to a particular company, but it was flexible enough that it covered the ways in which car finance is different to usual consumer lending. I played with it for days, but could not replicate its results. In the end, the CFO wrote to the author of the calculator, and paid good money for a copy of the code. Not only was the code bad, it was quite wrong, provably so. No wonder I couldn’t figure the numbers out!]
A few weeks ago, I idly started playing with the payment calculators on various lending sites. One in particular caught my eye, because it had a unique feature to it (which I’m not going to expand on, or it gives the company away). I realised that, behind-the-scenes, the website called a publicly-accessible API, so I coded up loops in SQL to automatically query the API with random values, and then analysed the results:
- The code on the website that called the API had at least two minor bugs, which meant changes to the UI elements weren’t reflected in the calls to the API.
- The JSON returned by the API call had some… interesting variable naming. I expect the code was imported from the US.
- The figures were being rounded, intra-calculation, in a way I’d not seen before.
- I could only replicate the results about 95% of the time — the other 5% only made sense if the interest rate was different to what was stated.
I’ve no idea how my investigation would be received if I emailed the company about my findings. I’ll keep it under my hat for now.
LoanSims
It’s been at the back of my mind for years to create a lending company simulator: that is, starting with a budget of X, lend out Y new loans per day (according to some distribution of terms/amounts), collect payments from pre-existing loans (subject to parameters relating to propensity to pay back), and track the performance of the company day-by-day. As part of my long-term goal to make my ‘go-to’ coding language Python, I wrote LoanSims, my first pass at this app (named for legendary ‘Carry On’ actress Joan Sims).
I was flush with enthusiasm for it, but then spoke to a friend who has much more experience in the industry — he said he’d tried in the past to sell something similar to lenders, but weirdly there was little-to-no interest. Which dampened my excitement — there’s no point spending ages on code no-one will ever want, even if it is stimulating, academically, to build it. Onto the back-burner it goes!
Sus(s) Udio?
Udio is the latest AI music generator, easily stealing the crown from Suno. From their About Us page:
Udio builds AI tools to enable the next generation of music creators. We believe AI has the potential to expand musical horizons and enable anyone to create extraordinary music.
udio.com allows users to create music from simple text prompts by specifying topics, genres, and other descriptors which are then transformed into professional quality tracks.
At time of writing, I have generated 315 pieces of music using Udio — it’s currently in beta, and free (there are daily limits, but I’ve not hit them yet). You start off by giving a text prompt — e.g. “A song about beating the devil at poker, then drinking his whiskey and stealing his horse. Nineties shoegaze” — which returns two 33 second pieces of music. You can then extend either piece of music (choosing between intro / before / after / outro), or ‘remix’ it — then keep repeating the process.
The results are stunning. Eight times out of ten, the music is indistinguishable from a genuine track. It’s not perfect, and the auto-generated lyrics are cringey — but the vocals are so strong, it doesn’t seem to matter so much. (And you can provide your own lyrics!)
Because it’s one of my favourite periods, I’ve been generating music that sounds like 1970s art-rock, and while I can’t get anything that sounds close to David Bowie (you can’t use real artist names in the prompts), it’s pretty easy to get vocals that resemble Russell Mael (from Sparks), Jon Anderson (from Yes), or even Peter Gabriel or Phil Collins! (Although, so far, not Ian Anderson from Jethro Tull.)
I’ve had trouble with it not adhering to the prompt, e.g. trying to get a Eurovision-sounding song from the 1970s, it gives me 80s metal, or 2000s pop. But because it only takes a few seconds to re-generate a new piece of music, you can just try again.
It really is quite addictive. I know some people out there will be horrified at the idea of it: “music is art, it’s not the domain of algorithms”. Really, I have no answer; I have a houseful of keyboards and guitars, and I don’t feel it’s taking away from my enjoyment of making my own music. But then again, I don’t earn my living from making music.
Other ‘stuff’
- Applying for jobs: It seems it’s not a good time to be applying for tech jobs: it’s very difficult to get recruiters to call or email back, I’ve not had a single acknowledgement for any role I’ve applied for, whether directly or through a recruitment site, or LinkedIn. Trying times!
- AI not coming for us — yet: A few weeks ago, a story did the rounds about an AI developer, ‘Devin’, that looked like it could replace a whole software engineer; luckily, the truth was rather less sensational. This video is well worth 25 minutes of anyone’s time: Debunking Devin: “First AI Software Engineer” Upwork lie exposed!.
- Pete Tonkin, Hardware Guy: I am not a hardware guy. Yes I build my own computers, but inside they are a MESS. However, I had to replace the screen on my son’s laptop recently, and it went smoother than these things usually do! I’ll take the win.
- General file admin: I have 25 years of hard drives, CD-Rs and DVD-Rs with umpteen projects in various states of completion, so I’ve tasked myself with collating all the files, with a view to creating ONE single repository of all my work. It’s a nice goal to have, but realistically the chances of me completing the task are slim-to-none, because something more interesting is always just around the corner…
I might do more of these ‘diary entries’, if only to remind myself what it is I’ve been doing, and what tech I’ve been playing around with!
Converting R code to Python: A Test
Background
I’ve been using R as my statistics programming environment for decades. In fact, I started out using its predecessor, an earlier (non-open source) program called S. In the last 10 or so years, the role of data scientist has appeared, and in the main, data scientists use the Python programming language, which comes with its own many statistical libraries. In terms of statistics, there’s a huge overlap in the capabilities of R and Python — pretty much anything you can do in R, you can do in Python, and vice-versa. Of course, Python is also used everywhere these days in places you wouldn’t consider using R, e.g. building apps or websites.
In 2020, I completed a few Python modules at DataCamp, but haven’t needed to use it much since. However, Python is pretty much the #1 requirement when it comes to applying for data science positions, so I need to get comfortable with it.
Where I have my own (fairly extensive) library of R functions for various modelling, credit risk and maths purposes, it’d be nice to port all that code to Python — and even nicer if it could be done with as little effort as possible on my part! 😀 So let’s see how we get on with a small-ish function.
The function in R
In the 90s, my MSc dissertation was concerned with exact tests in contingency tables: see the wiki entry Fisher’s exact test for more details. In the PhD thesis of Dr Danius Michaelides, Exact Tests via Complete Enumeration: A Distributed Computing Approach, he describes an algorithm that enumerates integer partitions, subject to constraints (see Section 2.3.2 and Figure 2.2). This algorithm is used in the enumeration of contingency tables with fixed margins.
I’ve recreated this algorithm from his description. The function partitions_with_constraints takes two parameters: v, an array of maximum values, and N, the integer to be partitioned; and returns a matrix of all the possible partitions.
partitions_with_constraints <- function(v, N)
{
vl = length(v);
stopifnot( vl >= 2 );
stopifnot( all(v > 0) );
stopifnot( N <= sum(v) );
cells = rep(NA,vl);
res = c();
i = 1;
while (i > 0)
{
allocated = if(i==1) 0 else sum(cells[1:(i-1)]);
remaining = N - allocated;
if (i == vl) {
# We're at the last cell,
# which can only be the amount remaining.
# Add the cells array to our results.
cells[i] = remaining;
res = rbind(res, cells);
i = i - 1
} else {
if (is.na(cells[i])) {
# Set the cell to the minimum possible
cells[i] = max(0, remaining - sum(v[(i+1):vl]));
} else {
# Otherwise, increment
cells[i] = cells[i] + 1
}
cell.max = min(remaining, v[i]);
if (cells[i] > cell.max) {
# We've hit the maximum; blank the cell, and move back one
cells[i] = NA;
i = i - 1;
} else {
i = i + 1;
}
}
}
# Restore the indices on the rows:
res = matrix(res, nrow=dim(res)[1], byrow=F);
return(res);
}
If we run it using the example from the thesis, we get:
partitions_with_constraints(c(3,1,3),4);[,1] [,2] [,3] [1,] 0 1 3 [2,] 1 0 3 [3,] 1 1 2 [4,] 2 0 2 [5,] 2 1 1 [6,] 3 0 1 [7,] 3 1 0
To explain the output in words: there are 7 ways in which we can partition the number 4 into sums of three numbers, where the first number is between 0 and 3, the second number is between 0 and 1, and the third number is between 0 and 3.
Converting the code with Copilot
For the conversion, I used Microsoft Copilot. (There is a ‘pro’ version, but I’m using the free website version.) If you google Copilot, it seems to be referred to mainly as a ‘chatbot’, but that term feels rather inadequate — so I asked Copilot how to describe itself! It said “Microsoft Copilot is an AI companion designed to assist users by providing information, answering questions, and engaging in conversation.” An ‘AI companion’ is a better description, I think.
Into the ‘Ask me anything’ box, I wrote “Please convert this R function into Python:” and pasted in the code from above. Within a few seconds, it returned the Python code you see below.
The function in Python
Here’s the code that Copilot returned:
import numpy as np
def partitions_with_constraints(v, N):
vl = len(v)
assert vl >= 2
assert all(i > 0 for i in v)
assert N <= sum(v)
cells = [None]*vl
res = []
i = 1
while i > 0:
allocated = 0 if i == 1 else sum(cells[0:i-1])
remaining = N - allocated
if i == vl:
cells[i-1] = remaining
res.append(cells[:])
i -= 1
else:
if cells[i-1] is None:
cells[i-1] = max(0, remaining - sum(v[i:vl]))
else:
cells[i-1] += 1
cell_max = min(remaining, v[i-1])
if cells[i-1] > cell_max:
cells[i-1] = None
i -= 1
else:
i += 1
return np.array(res)
It also output some additional text, describing how the primitive function calls are translated: This Python function should behave similarly to your R function. Please note that Python uses 0-based indexing, unlike R which uses 1-based indexing. This means that some adjustments were needed in the translation. Also, Python does not have a direct equivalent to R’s NA, so None is used instead. The rbind function in R is replaced with list append and conversion to a numpy array in Python. The stopifnot function in R is replaced with assert in Python. The all and sum functions in Python behave similarly to their counterparts in R. The length function in R is replaced with len in Python. The rep function in R is replaced with list multiplication in Python. The max and min functions in Python behave similarly to their counterparts in R.
For me, the most impressive thing is the conversion of arrays from 1-based (i.e. arrays are indexed starting from 1 in R) to 0-based (as in Python, like e.g. C/C++); that wasn’t specified anywhere in the instructions, it just ‘knew’ to do it.
I often use one of the following online sites to run Python (if the code isn’t too big):
I used the first one, pasting the code in, plus the call to the function with the same parameters as in the R version:
print(partitions_with_constraints([3,1,3],4))
[[0 1 3]
[1 0 3]
[1 1 2]
[2 0 2]
[2 1 1]
[3 0 1]
[3 1 0]]
which you can check is the same data as the R version, only the output formatting is different.
Full disclosure: the code didn’t run first time — I’d made a tiny mistake in one of my assertions, that Copilot just translated without picking up on it. And when it did run, there was no output, because I’d forgotten the print(…) around the function call. But these were very minor issues, fixed in seconds.
Copilot generating Python from a description
I want to present another instance of Copilot helping with coding; in this case, not a conversion, but coding an algorithm from a description of what the algorithm should do.
As I’ve mentioned before (in the post Practical queries using binary operations), I occasionally toy around with a Sudoku solver I’ve written in SQL. There’s one piece of cell-checking code I haven’t written yet, so could Copilot write it for me?
My prompt:“I am writing a Sudoku solver. For each remaining cell, I have a list of possible values for that cell. For example, I have a row where four cells have been filled in: 1,4,6 and 8. The remaining possible cell values are (3,7),(2,3,5),(2,3,9),(5,9),(5,7,9) , where parentheses delimit cells. What algorithm can I use to find all possible combinations of cell values? E.g. for my example here, 3,2,9,5,7 is a possibility.”
Copilot wrote: You can use a recursive backtracking algorithm to find all possible combinations of cell values. Here’s a Python function that does this:
def solve_sudoku(possible_values, index=0, solution=[]):
if index == len(possible_values):
print(solution)
return
for value in possible_values[index]:
if value not in solution:
solve_sudoku(possible_values, index + 1, solution + [value])
possible_values = [(3,7),(2,3,5),(2,3,9),(5,9),(5,7,9)]
solve_sudoku(possible_values)
Looks promising… here’s the output when I run the code at Online Python:
[3, 2, 9, 5, 7]
[3, 5, 2, 9, 7]
[7, 2, 3, 5, 9]
[7, 2, 3, 9, 5]
[7, 3, 2, 5, 9]
[7, 3, 2, 9, 5]
It worked, first time.
Then, I asked if it could convert the code to SQL:
SQL is not designed for this type of recursive computation... That being said, you can use recursive Common Table Expressions (CTEs) in SQL for some types of recursive problems. However, the problem you’re trying to solve involves backtracking, which is not straightforward to implement in SQL.
Oh well, you can’t win them all! [I’m not entirely sure how I’m going to convert it to SQL, but the Python code has given me some ideas, so that’s better than nothing.]
Final thoughts
This all worked much better than I thought it would, and the odds are very good that I can convert all my R code to Python without too much work. Sure, the algorithms above aren’t that complicated, but none of my R code is that complicated; most of the functions are wrappers around calls to R libraries. If direct conversion doesn’t work, I can try asking Copilot to recreate the code from a text description of the input/output.
Getting to grips with missing pennies
Over the past few years, I’ve created an awful lot of amortization schedules for loans, by hand and via code. As I read around the subject, I kept seeing references to ‘missing pennies’, but never really understood what was being written about. The way I do my calculations, I don’t get any ‘missing pennies’!
At last, with the help of Microsoft Copilot, I finally understand. I asked Copilot to look at the webpage Business Math: 13.3 AMORTIZATION SCHEDULE, and explain the ‘missing pennies’ section, and it did just that, first time.
As a result, I’ve written a document explaining the process:
Click here for the PDF document ‘Explaining Missing Pennies in Amortization Schedules’
So why hadn’t I understood it before now? Because I think about loan schedules as a programmer, and ‘missing pennies’ are only a thing if you do all the rounding at the same time, and it wouldn’t cross my mind to do that.
SQL Pete 
Recent Comments