Created
March 24, 2015 09:14
-
-
Save nakagami/1972b48b687c56934ed8 to your computer and use it in GitHub Desktop.
Test for REGR_XXXX() function with Firebird3
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # coding:utf-8 | |
| import unittest | |
| import tempfile | |
| import firebirdsql | |
| values_with_null = [ | |
| (1, 1), | |
| (2, 2), | |
| (3, 4), | |
| (4, None), | |
| (5, 16), | |
| (None, 32), | |
| (7, 64), | |
| (8, 128), | |
| (9, 256), | |
| (10, 512), | |
| ] | |
| values_without_null = [ | |
| (1, 1), | |
| (2, 2), | |
| (3, 4), | |
| (4, 8), | |
| (5, 16), | |
| (6, 32), | |
| (7, 64), | |
| (8, 128), | |
| (9, 256), | |
| (10, 512), | |
| ] | |
| class TestAggregate(unittest.TestCase): | |
| host='localhost' | |
| port=3050 | |
| user='sysdba' | |
| password='masterkey' | |
| def setUp(self): | |
| self.database=tempfile.mktemp() | |
| self.connection = firebirdsql.create_database( | |
| host=self.host, | |
| port=self.port, | |
| database=self.database, | |
| user=self.user, | |
| password=self.password) | |
| cur = self.connection.cursor() | |
| cur.execute(""" | |
| create table test_int_with_null ( | |
| x integer, | |
| y integer | |
| ) | |
| """) | |
| cur.execute(""" | |
| create table test_double_with_null ( | |
| x double precision, | |
| y double precision | |
| ) | |
| """) | |
| cur.execute(""" | |
| create table test_int_without_null ( | |
| x integer, | |
| y integer | |
| ) | |
| """) | |
| cur.execute(""" | |
| create table test_double_without_null ( | |
| x double precision, | |
| y double precision | |
| ) | |
| """) | |
| self.connection.commit() | |
| self.connection.begin() | |
| cur = self.connection.cursor() | |
| for x, y in values_with_null: | |
| cur.execute("insert into test_int_with_null (X, Y) values (?, ?)", (x, y)) | |
| cur.execute("insert into test_double_with_null (X, Y) values (?, ?)", (x, y)) | |
| for x, y in values_without_null: | |
| cur.execute("insert into test_int_without_null (X, Y) values (?, ?)", (x, y)) | |
| cur.execute("insert into test_double_without_null (X, Y) values (?, ?)", (x, y)) | |
| self.connection.commit() | |
| def tearDown(self): | |
| self.connection.close() | |
| def test_regr_avgx(self): | |
| cur = self.connection.cursor() | |
| cur.execute("select regr_avgx(y, x) from test_double_with_null") | |
| self.assertEqual(cur.fetchone()[0], 5.625) | |
| cur.execute("select regr_avgx(y, x) from test_double_without_null") | |
| self.assertEqual(cur.fetchone()[0], 5.5) | |
| cur.execute("select regr_avgx(y, x) from test_int_with_null") | |
| self.assertEqual(cur.fetchone()[0], 5.625) | |
| cur.execute("select regr_avgx(y, x) from test_int_without_null") | |
| self.assertEqual(cur.fetchone()[0], 5.5) | |
| def test_regr_avgy(self): | |
| cur = self.connection.cursor() | |
| cur.execute("select regr_avgy(y, x) from test_double_with_null") | |
| self.assertEqual(cur.fetchone()[0], 122.875) | |
| cur.execute("select regr_avgy(y, x) from test_double_without_null") | |
| self.assertEqual(cur.fetchone()[0], 102.3) | |
| cur.execute("select regr_avgy(y, x) from test_int_with_null") | |
| self.assertEqual(cur.fetchone()[0], 122.875) | |
| cur.execute("select regr_avgy(y, x) from test_int_without_null") | |
| self.assertEqual(cur.fetchone()[0], 102.3) | |
| def test_regr_count(self): | |
| cur = self.connection.cursor() | |
| cur.execute("select regr_count(y, x) from test_double_with_null") | |
| self.assertEqual(cur.fetchone()[0], 8) | |
| cur.execute("select regr_count(y, x) from test_double_without_null") | |
| self.assertEqual(cur.fetchone()[0], 10) | |
| cur.execute("select regr_count(y, x) from test_int_with_null") | |
| self.assertEqual(cur.fetchone()[0], 8) | |
| cur.execute("select regr_count(y, x) from test_int_without_null") | |
| self.assertEqual(cur.fetchone()[0], 10) | |
| def test_regr_intercept(self): | |
| cur = self.connection.cursor() | |
| cur.execute("select regr_intercept(y, x) from test_double_with_null") | |
| self.assertEqual(cur.fetchone()[0], -121.04225352112675) | |
| cur.execute("select regr_intercept(y, x) from test_double_without_null") | |
| self.assertEqual(cur.fetchone()[0], -137.06666666666666) | |
| cur.execute("select regr_intercept(y, x) from test_int_with_null") | |
| self.assertEqual(cur.fetchone()[0], -121.04225352112675) | |
| cur.execute("select regr_intercept(y, x) from test_int_without_null") | |
| self.assertEqual(cur.fetchone()[0], -137.06666666666666) | |
| def test_regr_r2(self): | |
| cur = self.connection.cursor() | |
| cur.execute("select regr_r2(y, x) from test_double_with_null") | |
| self.assertEqual(cur.fetchone()[0], 0.6597532469466092) | |
| cur.execute("select regr_r2(y, x) from test_double_without_null") | |
| self.assertEqual(cur.fetchone()[0], 0.6381409401937261) | |
| cur.execute("select regr_r2(y, x) from test_int_with_null") | |
| self.assertEqual(cur.fetchone()[0], 0.6597532469466092) | |
| cur.execute("select regr_r2(y, x) from test_int_without_null") | |
| self.assertEqual(cur.fetchone()[0], 0.6381409401937261) | |
| def test_regr_slope(self): | |
| cur = self.connection.cursor() | |
| cur.execute("select regr_slope(y, x) from test_double_with_null") | |
| self.assertEqual(cur.fetchone()[0], 43.36306729264476) | |
| cur.execute("select regr_slope(y, x) from test_double_without_null") | |
| self.assertEqual(cur.fetchone()[0], 43.52121212121212) | |
| cur.execute("select regr_slope(y, x) from test_int_with_null") | |
| self.assertEqual(cur.fetchone()[0], 43.36306729264476) | |
| cur.execute("select regr_slope(y, x) from test_int_without_null") | |
| self.assertEqual(cur.fetchone()[0], 43.52121212121212) | |
| def test_regr_sxx(self): | |
| cur = self.connection.cursor() | |
| cur.execute("select regr_sxx(y, x) from test_double_with_null") | |
| self.assertEqual(cur.fetchone()[0], 79.875) | |
| cur.execute("select regr_sxx(y, x) from test_double_without_null") | |
| self.assertEqual(cur.fetchone()[0], 82.5) | |
| cur.execute("select regr_sxx(y, x) from test_int_with_null") | |
| self.assertEqual(cur.fetchone()[0], 79.875) | |
| cur.execute("select regr_sxx(y, x) from test_int_without_null") | |
| self.assertEqual(cur.fetchone()[0], 82.5) | |
| def test_regr_sxy(self): | |
| cur = self.connection.cursor() | |
| cur.execute("select regr_sxy(y, x) from test_double_with_null") | |
| self.assertEqual(cur.fetchone()[0], 3463.625) | |
| cur.execute("select regr_sxy(y, x) from test_double_without_null") | |
| self.assertEqual(cur.fetchone()[0], 3590.5) | |
| cur.execute("select regr_sxy(y, x) from test_int_with_null") | |
| self.assertEqual(cur.fetchone()[0], 3463.625) | |
| cur.execute("select regr_sxy(y, x) from test_int_without_null") | |
| self.assertEqual(cur.fetchone()[0], 3590.5) | |
| def test_regr_syy(self): | |
| cur = self.connection.cursor() | |
| cur.execute("select regr_syy(y, x) from test_double_with_null") | |
| self.assertEqual(cur.fetchone()[0], 227650.875) | |
| cur.execute("select regr_syy(y, x) from test_double_without_null") | |
| self.assertEqual(cur.fetchone()[0], 244872.09999999998) | |
| cur.execute("select regr_syy(y, x) from test_int_with_null") | |
| self.assertEqual(cur.fetchone()[0], 227650.875) | |
| cur.execute("select regr_syy(y, x) from test_int_without_null") | |
| self.assertEqual(cur.fetchone()[0], 244872.09999999998) | |
| if __name__ == "__main__": | |
| unittest.main() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment