1

For test purposes, I want to create data sets (tables) where the statistic distribution of the values in the columns is the same as my actual data, but randomized.

Example : consider the following table ('ARTICLES' for a clothes shop):

| id | type    | colour | size | price |
----------------------------------------
| 1 | shirt    | yellow | M    | 14.99 |
| 2 | trousers | brown  | L    | 20.00 |
| 3 | shirt    | red    | L    | 14.99 |
| 4 | trousers | red    | L    | 20.00 |
| 5 | cap      | yellow | M    | 5.00  |
| 6 | cap      | brown  | S    | 5.00  |
| 7 | shirt    | red    | M    | 14.99 |
| 8 | trousers | red    | L    | 20.00 |

So, out of these 8 rows, I have the following distributions for each column:

  • type : 3 shirts, 3 trousers and 2 cap
  • colour : 4 red, 2 yellow, 2 brown
  • size : 1 S, 3 M, 4 L
  • price : 2 x 5.00 , 3 x 14.99 , 3 x 20.00

What I want is to create another table, with the same columns and the same number of rows, in which I have for each separate column the same statistic distribution of values, but assigned randomly and independently from each other.

Doing the stats for each column is pretty easy :

SELECT column_name, COUNT(1) AS number_of_rows 
FROM ARTICLES GROUP BY column_name ;

I can also easily record these stats in a dedicated table :

CREATE TABLE articles_stats ( column_name varchar(255), value varchar(255), number_of_rows integer );

INSERT INTO articles_stats (column_name, value, number_of_rows)
SELECT 'type', type, COUNT(1) AS number_of_rows 
    FROM ARTICLES GROUP BY type 
UNION
SELECT 'colour', colour, COUNT(1) AS number_of_rows 
    FROM ARTICLES GROUP BY colour  
UNION
SELECT 'size', size, COUNT(1) AS number_of_rows 
    FROM ARTICLES GROUP BY size  
UNION
SELECT 'price', price::varchar(255), COUNT(1) AS number_of_rows 
    FROM ARTICLES GROUP BY price ;

In this example, it would create the following articles_stats table :

| column_name | value    | number_of_rows | 
-------------------------------------------
| type        | shirt    |        3       |
| type        | trousers |        3       |
| type        | cap      |        2       |
| colour      | red      |        4       |
| colour      | yellow   |        2       |
| colour      | brown    |        2       |
| size        |    S     |        1       |
| size        |    M     |        3       |
| size        |    L     |        4       |
| price       |    5.00  |        2       |
| price       |   14.99  |        3       |
| price       |   20.00  |        3       |

But how do I then create inserts into the target table (lets' call it 'RANDOM_ARTICLES') ?

PS: I have to do this many times so I hope to create a PSQL function for this.

1

If you only need the distribution of cardinalities to be the same, and not the ordering, or the length of the text strings, then you can use a hashing function.

create new_table as select 
    md5('Aiw7Pa6y'||col1) as col1, 
    md5('coh9ahMo'||col2) as col2,
    md5('ii5eFohj'||col3::text) as col3
from old_table;

For non-text columns, you would have to decide if they need to be randomized at all, and if so whether conversion to text (keeping the cardinality of each distinct value, but not the order or relative gaps) would be suitable.

  • Sorry, I don't understand what use a hashing function would be of in this case. Could you explain a bit ? – Darth Kangooroo Dec 16 '18 at 20:10
1

My understanding of the question: you want to shuffle the contents "vertically", inside the columns, loosing the intra-row relationships (which is a quite unnatural thing to do in SQL).

This can be done by self-joining on the row numbers reordered randomly, as many times as the number of columns to shuffle. The effect is to put together the type of a random row with the colour of another random row with the size of another random row and so on, never reusing the same row numbers within each column. If there are N columns, it uses N series of row numbers ordered randomly.

To simplify the query I'd suggest creating a reusable view that obtains one such series:

CREATE TEMPORARY VIEW v AS
  SELECT *, row_number() over (order by random()) as rn FROM articles;

Then the query producing the shuffled contents could be:

INSERT INTO random_articles
SELECT s1.type, s2.colour, s3.size, s4.price
 FROM (select type,rn from v) AS s1
 JOIN (select colour,rn from v) AS s2 USING (rn)
 JOIN (select size,rn from v) AS s3 USING (rn)
 JOIN (select price,rn from v) AS s4 USING (rn);
0

Based on Daniel's answer, I have created the following script to test it works properly (it does, thanks Daniel) and to illustrate for others how to do it :

-- Creating original data set
DROP TABLE IF EXISTS articles CASCADE ;
DROP TABLE IF EXISTS articles_stats ;
DROP TABLE IF EXISTS  random_articles ;

CREATE TABLE articles
( id serial, 
type varchar, 
colour varchar,
size char,
price numeric );

INSERT INTO articles
(  type ,  colour , size , price  )
values
('shirt','yellow','M',14.99),
('trousers','brown','L',20.00),
('shirt','red','L',14.99),
('trousers','red','L',20.00),
('cap','yellow','M',5.00),
('cap','brown','S',5.00),
('shirt','red','M',14.99),
('trousers','red','L',20.00)
;

-- SELECT * FROM articles ;

-- Creating stats

CREATE TABLE articles_stats ( column_name varchar(255), value varchar(255), number_of_rows integer );

INSERT INTO articles_stats (column_name, value, number_of_rows)
SELECT 'type', type, COUNT(1) AS number_of_rows 
    FROM ARTICLES GROUP BY type 
UNION
SELECT 'colour', colour, COUNT(1) AS number_of_rows 
    FROM ARTICLES GROUP BY colour  
UNION
SELECT 'size', size, COUNT(1) AS number_of_rows 
    FROM ARTICLES GROUP BY size  
UNION
SELECT 'price', price::varchar(255), COUNT(1) AS number_of_rows 
    FROM ARTICLES GROUP BY price ;

-- SELECT * FROM articles_stats ORDER BY column_name, value ;

-- Creating the copy table with randomized data

-- SELECT * FROM random_articles_view ;

CREATE TABLE random_articles
( id serial, 
type varchar, 
colour varchar,
size char,
price numeric )
;

-- DELETE FROM random_articles ;
DROP VIEW IF EXISTS random_articles_view ;
CREATE TEMPORARY VIEW random_articles_view AS -- randomizing actually takes place here
    SELECT *, row_number() over (order by random()) AS rn FROM articles;

INSERT INTO random_articles
(  type ,  colour , size , price  )
SELECT s1.type, s2.colour, s3.size, s4.price
    FROM (SELECT type,rn FROM random_articles_view) AS s1
    JOIN (SELECT colour,rn FROM random_articles_view) AS s2 USING (rn)
    JOIN (SELECT size,rn FROM random_articles_view) AS s3 USING (rn)
    JOIN (SELECT price,rn FROM random_articles_view) AS s4 USING (rn)
 ;

-- Comaring the tables' content themselves : searching for rows that match each others
SELECT r1.*
    FROM articles AS r1
    JOIN random_articles AS r2
        ON r1.type = r2.type
        AND r1.size = r2.size
        AND r1.colour = r2.colour
        AND r1.price = r2.price
;

-- Comparing the stats : if the SELECT query returns nothing, everthung went fine
SELECT r1.column_name, r1.value, r1.number_of_rows AS number_of_rows_original_table, r2.number_of_rows AS number_of_rows_copied_table
    FROM articles_stats AS r1
    FULL OUTER JOIN
    (   -- creating stats from the randomized table
        SELECT 'type' AS column_name, type AS value, COUNT(1) AS number_of_rows 
            FROM random_articles GROUP BY type 
        UNION
        SELECT 'colour' AS column_name, colour AS value, COUNT(1) AS number_of_rows 
            FROM random_articles GROUP BY colour  
        UNION
        SELECT 'size' AS column_name, size AS value, COUNT(1) AS number_of_rows 
            FROM random_articles GROUP BY size  
        UNION
        SELECT 'price' AS column_name, price::varchar(255)  AS value, COUNT(1) AS number_of_rows 
            FROM random_articles GROUP BY price 
    ) AS r2
    ON r1.column_name = r2.column_name AND r1.value = r2.value
    WHERE r1.number_of_rows != r2.number_of_rows
;

Now, the next step will be to create function(s) that can do that on any table.


Edit: I created a first version of the function. It works for tables that have at least one unique identifier (which must stay unchanged). It creates a copy of the table where the content of the fields specified in parameters will be randomly mixed.

CREATE OR REPLACE FUNCTION duplicate_table_with_mixed_data(input_table_name VARCHAR, output_table_name VARCHAR, unique_key_field_name VARCHAR, VARIADIC field_names VARCHAR[]) RETURNS void
AS $$
    DECLARE 
        temp_view_name TEXT;
        use_sql_1 TEXT;
        use_sql_2 TEXT;
        use_sql_3 TEXT;
        use_sql_4 TEXT;
        loop_counter INTEGER ;
        loop_max INTEGER ;
        field_name TEXT ;
    BEGIN
        temp_view_name := 'temp_randomizing_' || input_table_name ;
        -- Step 1 : randomizing data
        use_sql_1 := 'DROP VIEW IF EXISTS ' || temp_view_name || E' ;\n CREATE TEMPORARY VIEW ' || temp_view_name || ' AS SELECT *, row_number() over (order by random()) AS rn FROM ' || input_table_name || E';\n' ;
        EXECUTE use_sql_1 ;

        -- Step 2 : Copying table with data
        use_sql_2  := 'DROP TABLE IF EXISTS ' || output_table_name || E' ;\nCREATE TABLE ' || output_table_name || ' AS TABLE  ' || input_table_name || E' ;\n' ;
        EXECUTE use_sql_2 ;

        -- Step 3 : randomizing the data in the target fields
        loop_max := array_length(field_names,1) ;
        loop_counter := 1 ;
        use_sql_3 := 'UPDATE ' || output_table_name || E' AS out SET \n' ;
        FOREACH field_name IN ARRAY field_names
        LOOP
            use_sql_3 := use_sql_3 || field_name || ' = s' || loop_counter || '.' || field_name ;
            IF loop_counter < loop_max THEN 
                use_sql_3 := use_sql_3 || E',\n' ;
            END IF ;
            loop_counter := loop_counter +1 ;
        END LOOP ;
        use_sql_3 := use_sql_3 || E'\nFROM ' || temp_view_name || ' AS s0 ';
        loop_counter := 1;
        FOREACH field_name IN ARRAY field_names
        LOOP
            use_sql_3 := use_sql_3 || E'\nJOIN (SELECT ' || field_name || ', rn FROM ' || temp_view_name || ') AS s' || loop_counter || ' USING (rn) '; 
            loop_counter := loop_counter +1 ;
        END LOOP ;
        use_sql_3 := use_sql_3 || E'\nWHERE s0.' || unique_key_field_name || ' = out.' || unique_key_field_name || ';' ;
        EXECUTE use_sql_3 ;

    END ;
$$ LANGUAGE plpgsql ;

Call example :

CREATE TABLE articles
( id serial, 
article_name varchar,
type varchar, 
colour varchar,
size varchar(4),
price numeric );

INSERT INTO articles
( article_name, type ,  colour , size , price  )
values
('aaa','shirt','yellow','M',14.99),
('bbb','trousers','brown','L',20.00),
('ccc','shirt','red','L',14.99),
('ddd','trousers','red','L',20.00),
('eee','cap','yellow','M',5.00),
('fff','cap','brown','S',5.00),
('ggg','shirt','red','M',14.99),
('hhh','trousers','red','L',20.00),
('iii','underpants','white','X',4.00),
('jjj','jacket','blue','L',10.00)
;

SELECT duplicate_table_with_mixed_data('articles', 'randomized_articles', 'id', VARIADIC array['type','colour','size','price']);

Of course, there is still room for improvement. I had added a 4th step in the function, operating a select, also created dynamically, but I don't succeed in returning the result:

CREATE OR REPLACE FUNCTION duplicate_table_with_mixed_data(input_table_name VARCHAR, output_table_name VARCHAR, unique_key_field_name VARCHAR, VARIADIC field_names VARCHAR[])
RETURNS TABLE
(   
    column_name varchar(255), 
    column_value text, 
    number_of_rows_input_table bigint, 
    number_of_rows_output_table bigint
)
[ ... same first 3 steps as above ... ]
-- Step 4 : comparing statistically if the distribution is the same (should return no row)
        use_sql_4 := 'SELECT r1.column_name::varchar(255) ,  r1.column_value , r1.number_of_rows AS number_of_rows_input_table , r2.number_of_rows AS number_of_rows_output_table FROM (' ;
        loop_counter := 1;
        FOREACH field_name IN ARRAY field_names
        LOOP
            use_sql_4 := use_sql_4 || E'\nSELECT ''' || field_name || '''AS column_name,  ' || field_name || '::text AS column_value, COUNT(1) AS number_of_rows FROM '
                || input_table_name || ' GROUP BY '  || field_name ; 
            IF loop_counter < loop_max THEN 
                use_sql_4 := use_sql_4 || E'\nUNION' ;
            END IF ;
            loop_counter := loop_counter +1 ;
        END LOOP ;
        use_sql_4 := use_sql_4 || E'\n) AS r1 JOIN \n(' ;
        loop_counter := 1;
        FOREACH field_name IN ARRAY field_names
        LOOP
            use_sql_4 := use_sql_4 || E'\nSELECT ''' || field_name || '''AS column_name,  ' || field_name || '::text AS column_value, COUNT(1) AS number_of_rows FROM '
                || output_table_name || ' GROUP BY '  || field_name ; 
            IF loop_counter < loop_max THEN 
                use_sql_4 := use_sql_4 || E'\nUNION' ;
            END IF ;
            loop_counter := loop_counter +1 ;
        END LOOP ;
        use_sql_4 := use_sql_4 || E') AS r2 \nON r1.column_name = r2.column_name \nAND  r1.column_value = r2.column_value \nAND r1.number_of_rows != r2.number_of_rows ;' ;
        RETURN QUERY EXECUTE use_sql_4 ;

    END ;
$$ LANGUAGE plpgsql ;

The 'RETURN QUERY EXECUTE' at the end does not return the result of the SELECT.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.