Happy Pi Day! We thought it would be fun to calculate pi using SQL queries in ClickHouse. Here is what we came up with so far...

- This one uses the ClickHouse
`numbers_mt`

table function to return 1B rows and only takes 40ms to compute the calculation:

`SELECT 4 * sum(if(number % 2, -1, 1) / ((number * 2) + 1)) AS pi`

FROM numbers_mt(1000000000.)

┌────────────────pi─┐

│ 3.141592652589797 │

└───────────────────┘

1 row in set. Elapsed: 0.432 sec. Processed 1.00 billion rows, 8.00 GB (2.32 billion rows/s., 18.53 GB/s.)

- The following example also processes 1B numbers, just not as quickly:

`SELECT 3 + (4 * sum(if((number % 2) = 0, if((number % 4) = 0, -1 / ((number * (number + 1)) * (number + 2)), 1 / ((number * (number + 1)) * (number + 2))), 0))) AS pi`

FROM numbers_mt(2, 10000000000)

┌─────────────────pi─┐

│ 3.1415926525808087 │

└────────────────────┘

1 row in set. Elapsed: 9.825 sec. Processed 10.00 billion rows, 80.00 GB (1.02 billion rows/s., 8.14 GB/s.)

- This one is obviously our favorite in ClickHouse (and the most accurate!):

`SELECT pi()`

┌──────────────pi()─┐

│ 3.141592653589793 │

└───────────────────┘

1 row in set. Elapsed: 0.008 sec.

- Someone knew their trigonometry with this one:

`SELECT 2 * asin(1) AS pi`

┌────────────────pi─┐

│ 3.141592653589793 │

└───────────────────┘

1 row in set. Elapsed: 0.005 sec.

- Here is a handy API that lets you specify the number of digits you want:

`SELECT *`

FROM url('https://api.pi.delivery/v1/pi?start=0&numberOfDigits=100', 'JSONEachRow')

┌───────────────content─┐

│ 3.1415926535897933e99 │

└───────────────────────┘

1 row in set. Elapsed: 0.556 sec.

- This one is clever - it uses ClickHouse distance functions:

`WITH random_points AS`

(

SELECT (rand64(1) / pow(2, 64), rand64(2) / pow(2, 64)) AS point

FROM numbers(1000000000)

)

SELECT (4 * countIf(L2Norm(point) < 1)) / count() AS pi

FROM random_points

┌──────────pi─┐

│ 3.141627208 │

└─────────────┘

1 row in set. Elapsed: 4.742 sec. Processed 1.00 billion rows, 8.00 GB (210.88 million rows/s., 1.69 GB/s.)

- If you're a physicist, you will be content with this one:

`SELECT 22 / 7`

┌─────divide(22, 7)─┐

│ 3.142857142857143 │

└───────────────────┘

- Another indirect mehthod (this one came from Alexey Milovidov) that is accurate to 7 decimal places - and it's quick:

`WITH`

10 AS length,

(number / 1000000000.) * length AS x

SELECT pow((2 * length) * avg(exp(-(x * x))), 2) AS pi

FROM numbers_mt(1000000000.)

┌─────────────────pi─┐

│ 3.1415926890388595 │

└────────────────────┘

1 row in set. Elapsed: 1.245 sec. Processed 1.00 billion rows, 8.00 GB (803.25 million rows/s., 6.43 GB/s.)

note

If you have any more, we'd love for you to contribute. Thanks!