DROP TABLE IF EXISTS Tbl1;
CREATE TABLE Tbl1 (T1F0 varchar(5), T1F1 varchar(5));
INSERT INTO Tbl1 (T1F0, T1F1) VALUES ('a','c');
INSERT INTO Tbl1 (T1F0, T1F1) VALUES ('c','d');
INSERT INTO Tbl1 (T1F0, T1F1) VALUES ('a','e');
DROP TABLE IF EXISTS Tbl2;
CREATE TABLE Tbl2 (T2F0 varchar(5), T2F1 varchar(5), T2F2 varchar(5));
INSERT INTO Tbl2 (T2F0, T2F1, T2F2) VALUES ('X','U','P');
INSERT INTO Tbl2 (T2F0, T2F1, T2F2) VALUES ('X','V','Q');
INSERT INTO Tbl2 (T2F0, T2F1, T2F2) VALUES ('Y','V','R');
INSERT INTO Tbl2 (T2F0, T2F1, T2F2) VALUES ('Z','U','R');
DROP TABLE IF EXISTS Table1x2;
CREATE TABLE Table1x2 AS SELECT * FROM Tbl1 CROSS JOIN Tbl2;
DROP TABLE IF EXISTS Table1x2x1x2x1x2;
CREATE TABLE Table1x2x1x2x1x2 (t11 varchar(5), t12 varchar(5), t13 varchar(5), t14 varchar(5), t15 varchar(5)
,t21 varchar(5), t22 varchar(5), t23 varchar(5), t24 varchar(5), t25 varchar(5)
,t31 varchar(5), t32 varchar(5), t33 varchar(5), t34 varchar(5), t35 varchar(5));
INSERT INTO Table1x2x1x2x1x2
SELECT T1.T1F0 AS t11, T1.T1F1 AS t12, T1.T2F0 AS t13, T1.T2F1 AS t14, T1.T2F2 AS t15
, T2.T1F0 AS t21, T2.T1F1 AS t22, T2.T2F0 AS t23, T2.T2F1 AS t24, T2.T2F2 AS t25
, T3.T1F0 AS t31, T3.T1F1 AS t32, T3.T2F0 AS t33, T3.T2F1 AS t34, T3.T2F2 AS t35
FROM Table1x2 AS T1, Table1x2 AS T2, Table1x2 AS T3;
DO $$
BEGIN
IF EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'Table1x2x1x2x1x2') THEN
EXECUTE format('SELECT %L', 'Table1x2x1x2x1x2');
ELSE
EXECUTE format('SELECT %L', 'Table1x2');
END IF;
END $$;
DO $$
DECLARE
TableName TEXT := 'Table1x2x1x2x1x2';
Query TEXT;
FirstName TEXT;
BEGIN
-- Take an arbitrary column (@FirstName) from the decomposed table (@TableName)
Query := 'SELECT sysTbl.COLUMN_NAME ' ||
' FROM INFORMATION_SCHEMA.COLUMNS sysTbl ' ||
' WHERE sysTbl.TABLE_NAME = ''' || TableName || ''' ' ||
' LIMIT 1';
EXECUTE Query INTO FirstName;
END $$;
--Take an arbitrary value (@FirstValue) from the column @FirstName.
--It plays a role of the sorting variable with respect to which "evaluation to 0" and "derivation" are done.
DO $$
DECLARE
TableName text := 'Table1x2x1x2x1x2';
FirstName text;
FirstValue text;
BEGIN
IF EXISTS (SELECT * FROM information_schema.tables WHERE table_name = TableName) THEN
FirstName := (SELECT column_name FROM information_schema.columns WHERE table_name = TableName LIMIT 1);
ELSE
TableName := 'Table1x2';
FirstName := (SELECT column_name FROM information_schema.columns WHERE table_name = TableName LIMIT 1);
END IF;
EXECUTE 'SELECT ' || quote_ident(FirstName) || ' FROM ' || quote_ident(TableName) || ' ORDER BY ' || quote_ident(FirstName) || ' LIMIT 1' INTO FirstValue;
END $$;
--Compose attribute names for an intermediate table
--that is a processed product of "derivation" and "evaluation to 0".
DO
$$
DECLARE
TableName TEXT;
Query TEXT;
FirstName TEXT;
FirstValue TEXT;
Columns TEXT;
BEGIN
SELECT table_name
INTO TableName
FROM information_schema.tables
WHERE table_name = 'Table1x2x1x2x1x2';
IF TableName IS NULL THEN
TableName := 'Table1x2';
END IF;
--Take an arbitrary column (@FirstName) from the decomposed table (@TableName)
Query := 'SELECT column_name FROM information_schema.columns WHERE table_name = ''' || TableName || ''' LIMIT 1';
EXECUTE Query INTO FirstName;
--Take an arbitrary value (@FirstValue) from the column @FirstName.
--It plays a role of the sorting variable with respect to which "evaluation to 0" and "derivation" are done.
Query := 'SELECT ' || quote_ident(FirstName) || ' FROM ' || quote_ident(TableName) || ' ORDER BY ' || quote_ident(FirstName) || ' LIMIT 1';
EXECUTE Query INTO FirstValue;
--Generate the dynamic columns based on the table
Query := 'SELECT string_agg(''D.'' || quote_ident(column_name) || '' AS D_'' || quote_ident(column_name) || '',E.'' || quote_ident(column_name) || '' AS E_'' || quote_ident(column_name), '''')
FROM information_schema.columns
WHERE table_name = ''' || TableName || ''' AND column_name != ''' || FirstName || '''';
EXECUTE Query INTO Columns;
Query := 'SELECT ' || quote_literal(Columns) || ' || ''FROM (SELECT *,
CASE WHEN ' || quote_ident(FirstName) || ' = ' || quote_literal(FirstValue) || ' THEN 0
ELSE (LEAD(' || quote_ident(FirstName) || ') OVER (ORDER BY ' || quote_ident(FirstName) || ') - ' || quote_ident(FirstName) || ')::FLOAT / NULLIF(' || quote_ident(FirstName) || ',0) END AS Diff
FROM ' || quote_ident(TableName) || ') AS t
UNPIVOT (
Val FOR Col IN (' || Columns || ')
) AS u
PIVOT (
MAX(Val) FOR Type IN (D, E)
) AS p
ORDER BY Col, Type DESC';
RAISE NOTICE 'Query: %', Query;
END;
$$
--Compose condition to remove duplicate rows that do not affect to decision making:
--no difference by origin (D or E) for values in a attribute pair
DO $$
DECLARE
Duplicates TEXT;
Query TEXT;
BEGIN
SELECT string_agg('AND((T.D_' || column_name || '=R.D_' || column_name || ' AND ' ||
'T.E_' || column_name || '=R.E_' || column_name || ') OR ' ||
'(T.D_' || column_name || '=R.E_' || column_name || ' AND ' ||
'T.E_' || column_name || '=R.D_' || column_name || '))', '') INTO Duplicates
FROM information_schema.columns
WHERE table_name = 'TableName' AND column_name != 'FirstName';
Query := 'SELECT ' || Duplicates || ' FROM intermediate_table T JOIN intermediate_table R ON T.rowid <> R.rowid';
RAISE NOTICE '%', Query;
--EXECUTE Query; -- Uncomment this line to execute the query
END $$;
--Compose attribute computations emulating differentiation with respect to a variable over F2
DO
$$
DECLARE Derivats TEXT;
BEGIN
EXECUTE 'SELECT string_agg(''IIF(T.D_'' || COLUMN_NAME || ''=V.D_'' || COLUMN_NAME || '',1,0) ^ ' ||
'IIF(T.E_'' || COLUMN_NAME || ''=V.D_'' || COLUMN_NAME || '',1,0) AS ' || COLUMN_NAME ||
', '''')' ||
' FROM INFORMATION_SCHEMA.COLUMNS' ||
' WHERE TABLE_NAME = ''' || TableName || '''' ||
' AND COLUMN_NAME != ''' || FirstName || '''' INTO Derivats;
Derivats := SUBSTRING(Derivats FROM 2);
END
$$;
--Create bit attribute definition for the decision table
DECLARE
BitColumns TEXT;
BEGIN
SELECT STRING_AGG(COLUMN_NAME || ' BIT', ',') INTO BitColumns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = TableName AND COLUMN_NAME <> FirstName;
RETURN BitColumns;
END;
--Create DecisonTable.
DROP TABLE IF EXISTS DecisionTable;
EXECUTE 'CREATE TABLE DecisionTable (' || @BitColumns || ')';
EXECUTE 'WITH Product AS (SELECT ' || @Columns ||
' FROM (SELECT * FROM ' || @TableName || ' WHERE ' || @FirstName || ' = ''' || @FirstValue || ''') AS D, ' ||
' (SELECT * FROM ' || @TableName || ' WHERE ' || @FirstName || '!= ''' || @FirstValue || ''') AS E) ' ||
'INSERT INTO DecisionTable ' ||
'SELECT DISTINCT ' || @Derivats ||
' FROM Product AS T, (SELECT * FROM Product LIMIT 1) AS V' ||
' WHERE 1 = (SELECT COUNT(*) FROM Product AS R WHERE ' || @Duplicates || ')';
--Sort columns by checking their values: all zeros means that
--the column belongs to the same component as @FirstName;
--otherwise the column belongs to the co-component.
DECLARE
BitColumns TEXT;
ColumnName TEXT;
FirstCompAttrs TEXT;
SecondCompAttrs TEXT;
Query TEXT;
ColumnCursor CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName AND COLUMN_NAME <> @FirstName;
BEGIN
--CREATE BIT COLUMNS FOR THE TEMPORARY TABLE
SELECT STRING_AGG( COLUMN_NAME || ' BIT', ',' ) INTO BitColumns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName AND COLUMN_NAME <> @FirstName;
--CREATE TEMPORARY TABLE FOR BIT VECTORS
Query := 'CREATE TEMPORARY TABLE DecisionTable (' || BitColumns || ');';
EXECUTE Query;
--INSERT BIT VECTORS INTO THE TEMPORARY TABLE
Query := 'WITH Product AS (SELECT ' || @Columns ||
' FROM (SELECT * FROM ' || @TableName || ' WHERE ' || @FirstName || ' = ''' || @FirstValue || ''') AS D, ' ||
' (SELECT * FROM ' || @TableName || ' WHERE ' || @FirstName || '!= ''' || @FirstValue || ''') AS E) ' ||
'INSERT INTO DecisionTable ' ||
'SELECT DISTINCT ' || @Derivats ||
' FROM Product AS T, (SELECT * FROM Product LIMIT 1) AS V' ||
' WHERE 1 = (SELECT COUNT(*) FROM Product AS R WHERE ' || @Duplicates || ');';
EXECUTE Query;
--SORT COLUMNS BY CHECKING THEIR VALUES
OPEN ColumnCursor;
FETCH NEXT FROM ColumnCursor INTO ColumnName;
WHILE (FOUND)
LOOP
Query := 'SELECT 1 FROM DecisionTable WHERE ' || ColumnName || ' = 1;';
EXECUTE Query;
IF (FOUND)
THEN
RAISE NOTICE 'First component %', ColumnName;
ELSE
RAISE NOTICE 'Second component %', ColumnName;
END IF;
FETCH NEXT FROM ColumnCursor INTO ColumnName;
END LOOP;
CLOSE ColumnCursor;
--CREATE RESULTING TABLES BEING COMPONENTS OF CARTESIAN DECOMPOSITION
--FirstCompAttrs and SecondCompAttrs must be initialized by the component's attributes
--Query := 'CREATE TABLE FirstComponent' ||
-- ' AS (SELECT DISTINCT ' || FirstCompAttrs ||
-- ' FROM ' || @TableName || '); ' ||
-- 'CREATE TABLE SecondComponent' ||
-- ' AS (SELECT DISTINCT ' || SecondCompAttrs ||
-- ' FROM ' || @TableName || '); ';
--EXECUTE Query;
END;
-- Create a temporary table for storing the Decision Table
DROP TABLE IF EXISTS DecisionTable;
CREATE TEMP TABLE DecisionTable AS
WITH Product AS (
SELECT *
FROM (
SELECT *
FROM @TableName
WHERE @FirstName = @FirstValue
) AS D
CROSS JOIN (
SELECT *
FROM @TableName
WHERE @FirstName != @FirstValue
) AS E
)
SELECT DISTINCT ON (@Derivats) @Derivats
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY @Derivats ORDER BY @BitColumns) AS row_num
FROM (
SELECT *,
CASE
WHEN @BitColumns = '' THEN 0
ELSE CAST(LEFT(@BitColumns, CHARINDEX(',', @BitColumns + ',') - 1) AS INTEGER)
END AS BitValue
FROM Product
) AS T
) AS T2
WHERE row_num = 1;
-- Sort columns by checking their values: all zeros means that
-- the column belongs to the same component as @FirstName;
-- otherwise the column belongs to the co-component.
DO $$
DECLARE
column_name VARCHAR;
column_cursor CURSOR FOR
SELECT column_name
FROM information_schema.columns
WHERE table_name = @TableName
AND column_name != @FirstName;
BEGIN
OPEN column_cursor;
LOOP
FETCH column_cursor INTO column_name;
EXIT WHEN NOT FOUND;
IF EXISTS (
SELECT 1
FROM DecisionTable
WHERE column_name = 1
) THEN
RAISE NOTICE 'First component %', column_name;
ELSE
RAISE NOTICE 'Second component %', column_name;
END IF;
END LOOP;
CLOSE column_cursor;
END $$;
-- Drop the Decision Table
DROP TABLE DecisionTable;
Telegram: DB developers channel