normal_rand(int numvals, float8 mean, float8 stddev) returns setof float8

normal_rand produce un conjunto de valores aleatorios con distribución normal (distribución gaussiana).

numvals es el número de valores que devolverá la función. mean es la media de la distribución normal de los valores y stddev es la desviación estándar de la distribución normal de los valores.

Por ejemplo, esta llamada solicita 1000 valores con una media de 5 y una desviación estándar de 3:

test=# SELECT * FROM normal_rand(1000, 5, 3);
     normal_rand
----------------------
     1.56556322244898
     9.10040991424657
     5.36957140345079
   -0.369151492880995
    0.283600703686639
       .
       .
       .
     4.82992125404908
     9.71308014517282
     2.49639286969028
(1000 rows)

crosstab(text sql)
crosstab(text sql, int N)

La función crosstab se utiliza para producir vistas “pivote”, en las que los datos se listan a lo largo de la página en lugar de hacia abajo. Por ejemplo, podríamos tener datos como

row1    val11
row1    val12
row1    val13
...
row2    val21
row2    val22
row2    val23
...

que deseamos mostrar como

row1    val11   val12   val13   ...
row2    val21   val22   val23   ...
...

La función crosstab recibe un parámetro de texto que es una consulta SQL que produce datos brutos formateados de la primera manera, y genera una tabla formateada de la segunda manera.

El parámetro sql es una sentencia SQL que produce el conjunto de datos de origen. Esta sentencia debe devolver una columna row_name, una columna category y una columna value. N es un parámetro obsoleto, ignorado si se proporciona (anteriormente este tenía que coincidir con el número de columnas de valor de salida, pero ahora eso se determina por la consulta que la invoca).

Por ejemplo, la consulta proporcionada podría producir un conjunto como este:

 row_name    cat    value
 ----------+-------+-------
  row1      cat1    val1
  row1      cat2    val2
  row1      cat3    val3
  row1      cat4    val4
  row2      cat1    val5
  row2      cat2    val6
  row2      cat3    val7
  row2      cat4    val8

La función crosstab está declarada para devolver setof record, por lo que los nombres y tipos reales de las columnas de salida deben definirse en la cláusula FROM de la sentencia SELECT que la invoca, por ejemplo:

SELECT * FROM crosstab('...') AS ct(row_name text, category_1 text, category_2 text);

Este ejemplo produce un conjunto como este:

           <== columnas de valor ==>
 row_name   category_1   category_2
----------+------------+------------
  row1        val1         val2
  row2        val5         val6

La cláusula FROM debe definir la salida como una columna row_name (del mismo tipo de datos que la primera columna de resultado de la consulta SQL) seguida de N columnas de value (todas del mismo tipo de datos que la tercera columna de resultado de la consulta SQL). Puedes configurar tantas columnas de valor de salida como desees. Los nombres de las columnas de salida dependen de ti.

La función crosstab produce una fila de salida por cada grupo consecutivo de filas de entrada con el mismo valor row_name. Llena las columnas de salida value, de izquierda a derecha, con los campos value de estas filas. Si hay menos filas en un grupo que columnas de salida value, las columnas de salida adicionales se llenan con nulos; si hay más filas, las filas de entrada adicionales se omiten.

En la práctica, la consulta SQL siempre debería especificar ORDER BY 1,2 to ensure that the input rows are properly ordered, that is, values with the same row_name are brought together and correctly ordered within the row. Notice that crosstab itself does not pay any attention to the second column of the query result; it's just there to be ordered by, to control the order in which the third-column values appear across the page.

En la práctica, la consulta SQL siempre debería especificar ORDER BY 1,2 para asegurar que las filas de entrada estén correctamente ordenadas, es decir, que los valores con el mismo row_name se agrupen y se ordenen correctamente dentro de la fila. Ten en cuenta que crosstab en sí no presta atención a la segunda columna del resultado de la consulta; sólo está ahí para ordenar por ella, para controlar el orden en que los valores de la tercera columna aparecen a lo largo de la página.

Aquí tienes un ejemplo completo:

CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT);
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att3','val3');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att4','val4');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att1','val5');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att2','val6');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att3','val7');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att4','val8');

SELECT *
FROM crosstab(
  'select rowid, attribute, value
   from ct
   where attribute = ''att2'' or attribute = ''att3''
   order by 1,2')
AS ct(row_name text, category_1 text, category_2 text, category_3 text);

 row_name | category_1 | category_2 | category_3
----------+------------+------------+------------
 test1    | val2       | val3       | 
 test2    | val6       | val7       | 
(2 rows)

Puedes evitar tener que escribir siempre una cláusula FROM para definir las columnas de salida configurando una función crosstab personalizada que tenga el tipo de fila de salida deseado incorporado en su definición. Esto se describe en la siguiente sección. Otra posibilidad es incrustar la cláusula FROM requerida en la definición de una vista.

crosstabN(text sql)

Las funciones crosstabN son ejemplos de cómo configurar envolturas (wrappers) personalizadas para la función general crosstab, de modo que no necesites escribir los nombres y tipos de columnas en la consulta SELECT invocadora. El módulo tablefunc incluye crosstab2, crosstab3 y crosstab4, cuyos tipos de fila de salida se definen como

CREATE TYPE tablefunc_crosstab_N AS (
    row_name TEXT,
    category_1 TEXT,
    category_2 TEXT,
        .
        .
        .
    category_N TEXT
);

Por lo tanto, estas funciones se pueden usar directamente cuando la consulta de entrada produce columnas row_name y value de tipo text, y deseas 2, 3 o 4 columnas de valores de salida. En todos los demás aspectos, se comportan exactamente como se describe anteriormente para la función general crosstab.

Por ejemplo, el ejemplo dado en la sección anterior también funcionaría como

SELECT *
FROM crosstab3(
  'select rowid, attribute, value
   from ct
   where attribute = ''att2'' or attribute = ''att3''
   order by 1,2');

Estas funciones se proporcionan principalmente con fines ilustrativos. Puedes crear tus propios tipos de retorno y funciones basadas en la función subyacente crosstab(). Hay dos formas de hacerlo:

crosstab(text source_sql, text category_sql)

La principal limitación de la forma de un solo parámetro de crosstab es que trata todos los valores de un grupo por igual, insertando cada valor en la primera columna disponible. Si quieres que las columnas de valores correspondan a categorías específicas de datos, y algunos grupos podrían no tener datos para algunas de las categorías, esto no funciona bien. La forma de dos parámetros de crosstab maneja este caso proporcionando una lista explícita de las categorías correspondientes a las columnas de salida.

source_sql es una sentencia SQL que produce el conjunto de datos de origen. Esta sentencia debe devolver una columna row_name, una columna category y una columna value. También puede tener una o más columnas “extra”. La columna row_name debe ser la primera. Las columnas category y value deben ser las últimas dos columnas, en ese orden. Cualquier columna entre row_name y category se trata como “extra”. Se espera que las columnas “extra” sean las mismas para todas las filas con el mismo valor row_name.

Por ejemplo, source_sql podría producir un conjunto como este:

SELECT row_name, extra_col, cat, value FROM foo ORDER BY 1;

 row_name    extra_col   cat    value
----------+------------+-----+---------
  row1         extra1    cat1    val1
  row1         extra1    cat2    val2
  row1         extra1    cat4    val4
  row2         extra2    cat1    val5
  row2         extra2    cat2    val6
  row2         extra2    cat3    val7
  row2         extra2    cat4    val8

category_sql es una sentencia SQL que produce el conjunto de categorías. Esta sentencia debe devolver sólo una columna. Debe producir al menos una fila, de lo contrario se generará un error. Además, no debe producir valores duplicados, o se generará un error. category_sql podría ser algo como:

SELECT DISTINCT cat FROM foo ORDER BY 1;
    cat
  -------
    cat1
    cat2
    cat3
    cat4

La función crosstab está declarada para devolver setof record, por lo que los nombres y tipos reales de las columnas de salida deben definirse en la cláusula FROM de la sentencia SELECT que la invoca, por ejemplo:

SELECT * FROM crosstab('...', '...')
    AS ct(row_name text, extra text, cat1 text, cat2 text, cat3 text, cat4 text);

Esto producirá un resultado como este:

                  <== columnas de valor ==>
row_name   extra   cat1   cat2   cat3   cat4
---------+-------+------+------+------+------
  row1     extra1  val1   val2          val4
  row2     extra2  val5   val6   val7   val8

La cláusula FROM debe definir el número correcto de columnas de salida de los tipos de datos adecuados. Si hay N columnas en el resultado de la consulta source_sql, las primeras N-2 de ellas deben coincidir con las primeras N-2 columnas de salida. Las columnas de salida restantes deben tener el tipo de la última columna del resultado de la consulta source_sql, y debe haber exactamente tantas como filas en el resultado de la consulta category_sql.

La función crosstab produce una fila de salida por cada grupo consecutivo de filas de entrada con el mismo valor row_name. La columna de salida row_name, más cualquier columna “extra”, se copian de la primera fila del grupo. Las columnas de salida value se llenan con los campos value de las filas que tienen valores category coincidentes. Si la categoría de una fila no coincide con ninguna salida de la consulta category_sql, su valor se ignora. Las columnas de salida cuya categoría coincidente no esté presente en ninguna fila de entrada del grupo se llenan con nulos.

En la práctica, la consulta source_sql siempre debería especificar ORDER BY 1 para asegurar que los valores con el mismo row_name se agrupen. Sin embargo, el orden de las categorías dentro de un grupo no es importante. Además, es esencial asegurarse de que el orden de salida de la consulta category_sql coincida con el orden de las columnas de salida especificado.

Aquí tienes dos ejemplos completos:

create table sales(year int, month int, qty int);
insert into sales values(2007, 1, 1000);
insert into sales values(2007, 2, 1500);
insert into sales values(2007, 7, 500);
insert into sales values(2007, 11, 1500);
insert into sales values(2007, 12, 2000);
insert into sales values(2008, 1, 1000);

select * from crosstab(
  'select year, month, qty from sales order by 1',
  'select m from generate_series(1,12) m'
) as (
  year int,
  "Jan" int,
  "Feb" int,
  "Mar" int,
  "Apr" int,
  "May" int,
  "Jun" int,
  "Jul" int,
  "Aug" int,
  "Sep" int,
  "Oct" int,
  "Nov" int,
  "Dec" int
);
 year | Jan  | Feb  | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov  | Dec
------+------+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------
 2007 | 1000 | 1500 |     |     |     |     | 500 |     |     |     | 1500 | 2000
 2008 | 1000 |      |     |     |     |     |     |     |     |     |      |
(2 rows)

CREATE TABLE cth(rowid text, rowdt timestamp, attribute text, val text);
INSERT INTO cth VALUES('test1','01 March 2003','temperature','42');
INSERT INTO cth VALUES('test1','01 March 2003','test_result','PASS');
INSERT INTO cth VALUES('test1','01 March 2003','volts','2.6987');
INSERT INTO cth VALUES('test2','02 March 2003','temperature','53');
INSERT INTO cth VALUES('test2','02 March 2003','test_result','FAIL');
INSERT INTO cth VALUES('test2','02 March 2003','test_startdate','01 March 2003');
INSERT INTO cth VALUES('test2','02 March 2003','volts','3.1234');

SELECT * FROM crosstab
(
  'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
  'SELECT DISTINCT attribute FROM cth ORDER BY 1'
)
AS
(
       rowid text,
       rowdt timestamp,
       temperature int4,
       test_result text,
       test_startdate timestamp,
       volts float8
);
 rowid |          rowdt           | temperature | test_result |      test_startdate      | volts
-------+--------------------------+-------------+-------------+--------------------------+--------
 test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |                          | 2.6987
 test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003 | 3.1234
(2 rows)

Puedes crear funciones predefinidas para evitar tener que escribir los nombres y tipos de las columnas de resultado en cada consulta. Consulta los ejemplos de la sección anterior. La función C subyacente para esta forma de crosstab se llama crosstab_hash.

connectby(text relname, text keyid_fld, text parent_keyid_fld
          [, text orderby_fld ], text start_with, int max_depth
          [, text branch_delim ])

La función connectby produce una visualización de datos jerárquicos que están almacenados en una tabla. La tabla debe tener un campo clave que identifique de forma única las filas, y un campo clave-padre que haga referencia al padre (si lo hay) de cada fila. connectby puede mostrar el subárbol que desciende de cualquier fila.

Table F.34 explica los parámetros.

Los campos clave y clave-padre pueden ser de cualquier tipo de datos, pero deben ser del mismo tipo. Ten en cuenta que el valor start_with debe ingresarse como una cadena de texto, independientemente del tipo del campo clave.

La función connectby está declarada para devolver setof record, por lo que los nombres y tipos reales de las columnas de salida deben definirse en la cláusula FROM de la sentencia SELECT que la invoca, por ejemplo:

SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
    AS t(keyid text, parent_keyid text, level int, branch text, pos int);

Las primeras dos columnas de salida se utilizan para la clave de la fila actual y la clave de su fila padre; deben coincidir con el tipo del campo clave de la tabla. La tercera columna de salida es la profundidad en el árbol y debe ser de tipo integer. Si se proporcionó un parámetro branch_delim, la siguiente columna de salida es la visualización de la rama y debe ser de tipo text. Finalmente, si se proporcionó un parámetro orderby_fld, la última columna de salida es un número secuencial y debe ser de tipo integer.

La columna de salida “branch” (rama) muestra la ruta de claves tomadas para llegar a la fila actual. Las claves están separadas por la cadena branch_delim especificada. Si no se desea la visualización de la rama, omite tanto el parámetro branch_delim como la columna de la rama en la lista de columnas de salida.

Si el orden de los hermanos del mismo padre es importante, incluye el parámetro orderby_fld para especificar por qué campo ordenar a los hermanos. Este campo puede ser de cualquier tipo de datos ordenable. La lista de columnas de salida debe incluir una columna final de tipo entero para el número secuencial si y sólo si orderby_fld es especificado.

Los parámetros que representan los nombres de tabla y campo se copian tal cual en las consultas SQL que connectby genera internamente. Por lo tanto, incluye comillas dobles si los nombres mezclan mayúsculas y minúsculas o contienen caracteres especiales. También es posible que necesites calificar el nombre de la tabla con el esquema.

En tablas grandes, el rendimiento será deficiente a menos que haya un índice en el campo clave-padre.

Es importante que la cadena branch_delim no aparezca en ningún valor de clave, de lo contrario connectby podría reportar incorrectamente un error de recursión infinita. Ten en cuenta que si branch_delim no se proporciona, se utiliza un valor por defecto de ~ para propósitos de detección de recursión.

Aquí tienes un ejemplo:

CREATE TABLE connectby_tree(keyid text, parent_keyid text, pos int);

INSERT INTO connectby_tree VALUES('row1',NULL, 0);
INSERT INTO connectby_tree VALUES('row2','row1', 0);
INSERT INTO connectby_tree VALUES('row3','row1', 0);
INSERT INTO connectby_tree VALUES('row4','row2', 1);
INSERT INTO connectby_tree VALUES('row5','row2', 0);
INSERT INTO connectby_tree VALUES('row6','row4', 0);
INSERT INTO connectby_tree VALUES('row7','row3', 0);
INSERT INTO connectby_tree VALUES('row8','row6', 0);
INSERT INTO connectby_tree VALUES('row9','row5', 0);

-- con rama, sin orderby_fld (el orden de los resultados no está garantizado)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~')
 AS t(keyid text, parent_keyid text, level int, branch text);
 keyid | parent_keyid | level |       branch
-------+--------------+-------+---------------------
 row2  |              |     0 | row2
 row4  | row2         |     1 | row2~row4
 row6  | row4         |     2 | row2~row4~row6
 row8  | row6         |     3 | row2~row4~row6~row8
 row5  | row2         |     1 | row2~row5
 row9  | row5         |     2 | row2~row5~row9
(6 rows)

-- sin rama, sin orderby_fld (el orden de los resultados no está garantizado)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)
 AS t(keyid text, parent_keyid text, level int);
 keyid | parent_keyid | level
-------+--------------+-------
 row2  |              |     0
 row4  | row2         |     1
 row6  | row4         |     2
 row8  | row6         |     3
 row5  | row2         |     1
 row9  | row5         |     2
(6 rows)

-- con rama, con orderby_fld (nota que row5 va antes que row4)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
 AS t(keyid text, parent_keyid text, level int, branch text, pos int);
 keyid | parent_keyid | level |       branch        | pos
-------+--------------+-------+---------------------+-----
 row2  |              |     0 | row2                |   1
 row5  | row2         |     1 | row2~row5           |   2
 row9  | row5         |     2 | row2~row5~row9      |   3
 row4  | row2         |     1 | row2~row4           |   4
 row6  | row4         |     2 | row2~row4~row6      |   5
 row8  | row6         |     3 | row2~row4~row6~row8 |   6
(6 rows)

-- sin rama, con orderby_fld (nota que row5 va antes que row4)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0)
 AS t(keyid text, parent_keyid text, level int, pos int);
 keyid | parent_keyid | level | pos
-------+--------------+-------+-----
 row2  |              |     0 |   1
 row5  | row2         |     1 |   2
 row9  | row5         |     2 |   3
 row4  | row2         |     1 |   4
 row6  | row4         |     2 |   5
 row8  | row6         |     3 |   6
(6 rows)