Skip to content

Instantly share code, notes, and snippets.

@carlsverre

carlsverre/samples.sql

Created March 14, 2020 16:15
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save carlsverre/08fcd301faa1bbb2f3fe7bc80da62d4a to your computer and use it in GitHub Desktop.
Save carlsverre/08fcd301faa1bbb2f3fe7bc80da62d4a to your computer and use it in GitHub Desktop.
Download ZIP
Interesting approximations of PI (and other ideas) - by MemSQL Engineering
Raw
samples.sql
create database if not exists pi;
use pi
create table if not exists mc (d int, key() using clustered columnstore);
create table if not exists tries(iter integer, pi float);
delimiter //
CREATE OR REPLACE PROCEDURE px(iter INTEGER) AS
DECLARE
q query(value FLOAT) =
select 4*sum(d)/count(d) from mc;
pi FLOAT;
BEGIN
delete from mc;
delete from tries;
insert into mc values (1-floor(pow(rand(),2)+pow(rand(),2)));
FOR i IN 1 .. iter LOOP
insert into mc (select (1-floor(pow(rand(),2)+pow(rand(),2))) from mc);
pi = scalar(q);
insert into tries values (i, pi);
END LOOP;
END //
DELIMITER ;
memsql> call px(20);
Query OK, 0 rows affected (0.65 sec)
memsql> select * from tries order by iter desc limit 10;
+------+---------+
| iter | pi |
+------+---------+
| 20 | 3.14454 |
| 19 | 3.14319 |
| 18 | 3.14456 |
| 17 | 3.13928 |
| 16 | 3.13794 |
| 15 | 3.1488 |
| 14 | 3.15527 |
| 13 | 3.14941 |
| 12 | 3.12695 |
| 11 | 3.17773 |
+------+---------+
10 rows in set (0.01 sec)
-----------------------------
create database pi;
use pi;
create table pi (x double, y double, d as 1-floor(x*x+y*y) persisted int);
insert into pi (select rand(),rand());
insert into pi (select x, y from (select rand() as x, rand() as y from pi));
insert into pi (select x, y from (select rand() as x, rand() as y from pi));
insert into pi (select x, y from (select rand() as x, rand() as y from pi));
insert into pi (select x, y from (select rand() as x, rand() as y from pi));
insert into pi (select x, y from (select rand() as x, rand() as y from pi));
insert into pi (select x, y from (select rand() as x, rand() as y from pi));
insert into pi (select x, y from (select rand() as x, rand() as y from pi));
insert into pi (select x, y from (select rand() as x, rand() as y from pi));
insert into pi (select x, y from (select rand() as x, rand() as y from pi));
insert into pi (select x, y from (select rand() as x, rand() as y from pi));
insert into pi (select x, y from (select rand() as x, rand() as y from pi));
insert into pi (select x, y from (select rand() as x, rand() as y from pi));
insert into pi (select x, y from (select rand() as x, rand() as y from pi));
insert into pi (select x, y from (select rand() as x, rand() as y from pi));
insert into pi (select x, y from (select rand() as x, rand() as y from pi));
insert into pi (select x, y from (select rand() as x, rand() as y from pi));
insert into pi (select x, y from (select rand() as x, rand() as y from pi));
insert into pi (select x, y from (select rand() as x, rand() as y from pi));
select 4*sum(d)/count(d) from pi;
+-------------------+
| 4*sum(d)/count(d) |
+-------------------+
| 3.1391 |
+-------------------+
1 row in set (0.01 sec)
memsql> insert into pi (select x, y from (select rand() as x, rand() as y from pi));
Query OK, 262144 rows affected (0.30 sec)
Records: 262144 Duplicates: 0 Warnings: 0
memsql> select 4*sum(d)/count(d) from pi;
+-------------------+
| 4*sum(d)/count(d) |
+-------------------+
| 3.1378 |
+-------------------+
1 row in set (0.02 sec)
memsql> insert into pi (select x, y from (select rand() as x, rand() as y from pi));
Query OK, 524288 rows affected (0.54 sec)
Records: 524288 Duplicates: 0 Warnings: 0
memsql> select 4*sum(d)/count(d) from pi;
+-------------------+
| 4*sum(d)/count(d) |
+-------------------+
| 3.1372 |
+-------------------+
1 row in set (0.02 sec)
memsql> insert into pi (select x, y from (select rand() as x, rand() as y from pi));
Query OK, 1048576 rows affected (1.18 sec)
Records: 1048576 Duplicates: 0 Warnings: 0
memsql> select 4*sum(d)/count(d) from pi;
+-------------------+
| 4*sum(d)/count(d) |
+-------------------+
| 3.1390 |
+-------------------+
1 row in set (0.05 sec)
memsql> insert into pi (select x, y from (select rand() as x, rand() as y from pi));
Query OK, 2097152 rows affected (2.51 sec)
Records: 2097152 Duplicates: 0 Warnings: 0
memsql> select 4*sum(d)/count(d) from pi;
+-------------------+
| 4*sum(d)/count(d) |
+-------------------+
| 3.1404 |
+-------------------+
1 row in set (0.08 sec)
memsql> insert into pi (select x, y from (select rand() as x, rand() as y from pi));
Query OK, 4194304 rows affected (5.46 sec)
Records: 4194304 Duplicates: 0 Warnings: 0
memsql> select 4*sum(d)/count(d) from pi;
+-------------------+
| 4*sum(d)/count(d) |
+-------------------+
| 3.1411 |
+-------------------+
1 row in set (0.20 sec)
--------------------------------------------
memsql> load data infile '/tmp/random2.csv' into table rand
fields terminated by ','
(@x,@y) set x = @x/65536, y = @y/65536;
Query OK, 5000 rows affected (0.01 sec)
memsql> select 4*sum(d)/count(d) from rand;
+-------------------+
| 4*sum(d)/count(d) |
+-------------------+
| 3.1640 |
+-------------------+
1 row in set (0.00 sec)
--------------------------------------------
create table pi (i int auto_increment primary key);
insert into pi values (null);
insert into pi select null from pi;
insert into pi select null from pi;
insert into pi select null from pi;
insert into pi select null from pi;
insert into pi select null from pi;
insert into pi select null from pi;
SELECT
CONCAT(
LPAD("", ROUND((20 - (SQRT(POW(20, 2) - POW(20 - (i - 1), 2)) * 2) / 2) * 2), " "),
"+",
LPAD("", 80 - (2 * ROUND((20 - (SQRT(POW(20, 2) - POW(20 - (i - 1), 2)) * 2) / 2) * 2)), " "),
"+"
) as pecan_pie
FROM pi order by i asc LIMIT 41;
+------------------------------------------------------------------------------------+
| pecan_pie |
+------------------------------------------------------------------------------------+
| ++ |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| + + |
| ++ |
+------------------------------------------------------------------------------------+
41 rows in set (0.113 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment