SQLize Online / PHPize Online  /  SQLtest Online

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;