MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for November, 2024

SQL Calculations #1

without comments

This was principally written for my SQL students but I thought it might be useful to others. SQL calculation are performed row-by-row in the SELECT-list. In its simplest form without even touching a table, you can add two literal numbers like this:

1

SELECT 2 + 2 AS result;

It will display the result of the addition to the column alias result as a derived table, or the following result:

+--------+
| result |
+--------+
|      4 |
+--------+
1 row in set (0.00 sec)

Unfortunately, the use of literal values as shown above doesn’t really let you see how the calculation is made row-by-row because it only returns one row. You can rewrite the two literal values into one variable by using a Common Table Expressions (CTEs). The CTE creates an struct tuple with only one x element. Another way to describe what the CTE does would say, it creates a derived table named struct with a single x column in the SELECT-list.

The CTE runs first, then a subsequent query may use the CTE’s derived table results. Below is a query that uses the value in the struct.x derived table (or references the struct tuple’s x element) twice while assigning the value to a new column alias, labelled result. The FROM clause places the struct tuple in the queries namespace, which lets you reference it in the SELECT-list.

1
2
3
4

WITH struct AS
 (SELECT 2 AS x)
SELECT struct.x + struct.x AS result
FROM  struct;

Like the literal example, it will display the result of the addition to the column alias result as a derived table of one row:

+--------+
| result |
+--------+
|      4 |
+--------+
1 row in set (0.00 sec)

Having laid a basis for a simple calculation in one row, let’s expand the example and demonstrate how to perform row-by-row calculations. The example requires introducing some new concepts. One uses the UNION ALL set operator to fabricate a CTE derived table with three rows. Another uses a comma within the WITH clause to create two derived tables or CTEs. The last uses the CROSS JOIN to add the single row CTE’s single y column to each of the rows returned by the multiple row CTE.

The CROSS JOIN is a Cartesian product, which multiplies the rows in one table against the rows in another table while adding the columns from each table. That means fabricating a table of one column and one row lets you put a variable into all the rows of another table or set of tables combined through an equijoin or non-equijoin operation.

The query below takes a struct1 derived table of one column and three rows and a struct2 derived table of one column and one row, then uses a CROSS JOIN to create a new derived table, which would be a table of two columns and three rows. The Cartesian product only provides the two columns that we will multiply to create new data.

The SELECT-list lets us fabricate a new column where we multiply the values of column x and column y to create a set of new results in column result.

1
2
3
4
5
6
7
8
9
10

WITH struct1 AS
 (SELECT 1 AS x UNION ALL
  SELECT 2 AS x UNION ALL
  SELECT 3 AS x)
, struct2 AS
 (SELECT 10 AS y)
SELECT struct1.x AS x
,      struct2.y AS y
,      struct1.x * struct2.y AS result
FROM  struct1 CROSS JOIN struct2;

The query returns the following results, which show the values used to calculate the result and the result:

+---+----+--------+
| x | y  | result |
+---+----+--------+
| 1 | 10 |     10 |
| 2 | 10 |     20 |
| 3 | 10 |     30 |
+---+----+--------+
3 rows in set (0.00 sec)

As a rule, the columns x and y would not be displayed in the final derived table. You would only see the result columns’ values.

Let’s use an example from Alan Bwaulieu’s Learning SQL book with a twist. Rather than manually fabricating the ordinal numbers twice, let’s use the scope reference of a subsequent CTE to reference an earlier CTE. That would create two ten row tables of one column each, or a Cartesian product of a 100 row table with two columns. Then, let’s use the SELECT-list lets us fabricate only a new column, which will add 1 to the numbers 0 to 99 to give us the numbers 1 to a 100.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

WITH ones AS
 (SELECT 0 AS x UNION ALL
  SELECT 1 AS x UNION ALL
  SELECT 2 AS x UNION ALL
  SELECT 3 AS x UNION ALL
  SELECT 4 AS x UNION ALL
  SELECT 5 AS x UNION ALL
  SELECT 6 AS x UNION ALL
  SELECT 7 AS x UNION ALL
  SELECT 8 AS x UNION ALL
  SELECT 9 AS x )
, tens AS
 (SELECT x * 10 AS x FROM ones)
SELECT ones.x + tens.x + 1 AS ordinal
FROM   ones CROSS JOIN tens
ORDER BY ordinal;

It returns the following result set:

+---------+
| ordinal |
+---------+
|       1 |
|       2 |
|       3 |
|       4 |
|       5 |
|       6 |
|       7 |
|       8 |
|       9 |
|      10 |
|      11 |
 ...
|      98 |
|      99 |
|     100 |
+---------+
100 rows in set (0.00 sec)

Moving on to more complex math, let’s create a numerals table with the result from our prior query. It will enable calculating the factors of exponents. The easiest way to create the table is shown below (only caveat is that it will build it with a biting rather than an int data type).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

CREATE TABLE numerals AS
WITH ones AS
 (SELECT 0 AS x UNION ALL
  SELECT 1 AS x UNION ALL
  SELECT 2 AS x UNION ALL
  SELECT 3 AS x UNION ALL
  SELECT 4 AS x UNION ALL
  SELECT 5 AS x UNION ALL
  SELECT 6 AS x UNION ALL
  SELECT 7 AS x UNION ALL
  SELECT 8 AS x UNION ALL
  SELECT 9 AS x )
, tens AS
 (SELECT x * 10 AS x FROM ones)
SELECT ones.x + tens.x + 1 AS ordinal
FROM   ones CROSS JOIN tens
ORDER BY ordinal;

Line #15 sets the column alias that determines the name of the column for the numerals table. It can be described after running the foregoing script in MySQL as:

+---------+--------+------+-----+---------+-------+
| Field   | Type   | Null | Key | Default | Extra |
+---------+--------+------+-----+---------+-------+
| ordinal | bigint | NO   |     | 0       |       |
+---------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)

The next query accepts a substitution variable into the WITH clause, which means an external program will call it. (Although, you could use a session level variable, which I would discourage.) This query returns the factors for any given exponent:

1
2
3
4
5
6
7
8
9

WITH magic AS
(SELECT %s AS vkey)
SELECT   CONCAT(magic.vkey,'^',LOG(magic.vkey,n.ordinal)) AS powers
,        n.ordinal AS result
FROM     numerals n CROSS JOIN magic
WHERE    MOD(n.ordinal,magic.vkey) = 0
AND      LOG(magic.vkey,n.ordinal) REGEXP '^[0-9]*$' 
OR       n.ordinal = 1
ORDER BY n.ordinal;

FYI, in the WHERE clause the regular expression is guarantees only rows returning integer values, and the 1 returns the identity property of an exponent raised to the zero power.

Assuming you created the numerals table, put the foregoing query in a query.sql file (because I was to lazy to write the full parameter handling), and you run it in the same directory as this Python program, it’ll take any valid integer as a value.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114

#!/usr/bin/python
# ------------------------------------------------------------
#  Name: power.py
#  Date: 19 Oct 2024
# ------------------------------------------------------------
#  Purpose:
#  -------
#    The program shows you how to provide a single agrument
#    to a query and print the formatted output.
#
#    You can call the program:
#
#    ./power.py 3
#
# ------------------------------------------------------------
 
# Import libraries.
import sys
import mysql.connector
from mysql.connector import errorcode
 
# ============================================================
#  Define a local padding function.
# ============================================================
def pad(valueIn):
  # Define local variable.
  padding = ''
 
  # Convert single digit numbers to strings.
  if isinstance(valueIn,int) and len(str(valueIn)) == 1:
    padding = ' '
 
  # Return padding space.
  return padding
# ============================================================
#  End local function defintion.
# ============================================================
 
# Define any local variables.
powerIn = 2
query = ""
 
# ============================================================
#  Capture argument list minus the program name.
# ============================================================
arguments = sys.argv[1:]
 
# ============================================================
#  If one or more arguments exists and the first one is an
#  a string that can cast to an int, convert it to an int,
#  assign it to a variable, and ignore any other arguments
#  in the list.
# ============================================================
if len(arguments) >= 1 and arguments[0].isdigit():
  powerIn = int(arguments[0])
 
# ============================================================
#  Use a try-catch block to read and parse a query from a
#  a file found in the same local directory as the Python
#  program.
# ============================================================
try:
  file = open('query.sql','r')
  query = file.read().replace('\n',' ').replace(';','')
  file.close()
 
except IOError:
  print("Could not read file:", fileName)
 
# ============================================================
#  Attempt connection in a try-catch block.
# ============================================================
# --------------------------------------------------------
#  Open connection, bind variable in query and format
#  query output before closing the cursor.
# --------------------------------------------------------
try:
  # Open connection.
  cnx = mysql.connector.connect(user='student', password='student',
                                host='127.0.0.1',
                                database='studentdb')
 
  # Create cursor.
  cursor = cnx.cursor()
 
  # Execute cursor, and coerce string to tuple.
  cursor.execute(query, (powerIn,))
 
  # Display the rows returned by the query.
  for (powers, result) in cursor:
    print((" {} is: {}").format(powers, pad(result) + str(result)))
 
  # Close cursor.
  cursor.close()
 
# --------------------------------------------------------
#  Handle MySQL exception 
# --------------------------------------------------------
except mysql.connector.Error as e:
  if e.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
  elif e.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print("Error code:", e.errno)        # error number
    print("SQLSTATE value:", e.sqlstate) # SQLSTATE value
    print("Error message:", e.msg)       # error message
 
# --------------------------------------------------------
#  Close connection after try-catch completes.
# --------------------------------------------------------
# Close the connection when the try block completes.
else:
  cnx.close()

If you forget to call it with a numeric parameter, it uses 2 as the default. You would call it as follows from a Linux prompt from the local directory:

./power.py

It returns:

 2^0 is:  1
 2^1 is:  2
 2^2 is:  4
 2^3 is:  8
 2^4 is: 16
 2^5 is: 32
 2^6 is: 64

If you call it with a numeric parameter, it uses the numeric value. You would call it as follows from a Linux prompt from the local directory:

./power.py 3

It returns:

 3^0 is:  1
 3^1 is:  3
 3^2 is:  9
 3^3 is: 27
 3^4 is: 81

As always, I hope the post helps folks sort out how and why things work.

Written by maclochlainn

November 17th, 2024 at 6:47 pm

Posted in MySQL,MySQL 8,MySQL Client,MySQL Connect,MySQL Connect/Python,Python,Python 3.x