0845 643 64 63

# Creating a Python function to calculate Pi

Pi is 3.14159 to 5 decimal places.
To work out Pi, we will be using Leibniz’s formula:
X = 4 – 4/3 + 4/5 – 4/7 + 4/9 – …
This series converges to Pi, the more terms that are added to the series, the closer the value is to Pi.
For the proof on why this series converges to Pi – https://proofwiki.org/wiki/Leibniz’s_Formula_for_Pi
There are several points to note about the series:

• It’s infinite, we need to find a way to continue adding term after term.
• The denominator of the fraction increases by 2 every term.
• The terms alternate between positive and negative.

Firstly, let’s create a function called pi.

To continue adding terms, let’s use a for loop. Every time the loop executes another term is added.

range(1,10) will produce the numbers 1, 2, … 9, 10.
However, before the loops starts, our variables’ initial values need to be set.

The pi series will start from 0.
n represents the numerator of our fractions which is the constant 4.
d represents the denominator of our fractions which starts as 1.

d needs to increase by 2 every loop, let’s use sum equals to do this.
Pi will also use a sum equals, a denotes our positive/negative function which we will get on to:

The only problem left is how to get a to alternate between 1 and -1.
This is where we introduce modulo.
Modulo outputs the remainder of a division and is denoted by %.

This example shows from numbers 1 to 4, modulo 2 alternates between 0 and 1.
If we multiple by 2 and minus 1 it will alternate between 1 and -1 which is what we require.

Putting everything together gives:

This isn’t close to 3.14159 at all.

However, we are only executing the loop 10 times, hence only 10 terms are being used to calculate Pi.

Increasing the number of loops to a million will change this:

There it is! A function written from scratch to calculate Pi.
To get a value even closer to Pi just increase the number of loops.

Finally, if you do wish to use Pi in python the easiest way is to use the numpy library, which has a pi constant stored.

# SQL LAST_VALUE() wrong results

You may have come across the following scenario when using the LAST_VALUE() function.

You want to find the first or last value loaded in a result set, therefore you use the FIRST_VALUE() and LAST_VALUE() functions like below:

You expect to get “A” for every record in the FirstValue column and “E” for every record in the LastValue column.
However you see the following output:

LAST_VALUE() on its own is implemented from the current row going back to the first row, for example:

• Row 1 – The only value in scope is A.
Hence, FIRST_VALUE() & LAST_VALUE() both return A.
• Row 2 – The values now in scope are A & B.
Hence, FIRST_VALUE() returns A and LAST_VALUE() returns B.
• Row 3 – The values now in scope are A, B, C.
Hence, FIRST_VALUE() returns A and LAST_VALUE() returns C.
• And so on…

To return the actual last value, add the additional clause ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. This ensures that for each row the LAST_VALUE() function looks at all rows; from the very first row to the very last row. Now, you have the following code (the original code with an extra column using the additional clause):

This gives you the following output, LastValue2 shows the true last value for the result set:

The Frog Blog Team Purple Frog specialise in designing and implementing Microsoft Data Analytics solutions, including Data Warehouses, Cubes, SQL Server, SSIS, ADF, SSAS, Power BI, MDX, DAX, Machine Learning and more.

This is a collection of thoughts, ramblings and ideas that we think would be useful to share.

Authors:

 Alex Whittles(MVP) Jeet Kainth Jon Fletcher Nick Edwards Joe Billingham Lewis Prince Reiss McSporran   Data Platform MVP 