Trading-DB

This repository contains material related to the thesis Armazenamento e Processamento de Dados de Comercio Internacional Usando TimescaleDB, including the SQL script to create the data base, the code for the implemented operations and the configuration of docker image that encapsulate the base.

Note: Arrays passed as input to functions, can have any dimensionality.

Make

trading-db$ make install-reqs: Install compilation requisites.

trading-db/Docker$ make build: Build the shared library and brudel/trading-db image.

trading-db/Docker$ make test: Create and start test container.

trading-db$ make start-test: Start test container.

trading-db$ make conn: Open psql client at test container.

trading-db$ make: Execute query specified at Makefile at test container.

Schema

continent

Column	Type	Constraints
code	char(2)	PRIMARY KEY
name	text

country

Column	Type	Constraints
code	char(3)	PRIMARY KEY
name	text
continent	char(2)	REFERENCES continent

country_annual_measure

Column	Type	Constraints
country	char(3)	PRIMARY KEY, REFERENCES country
year	integer	PRIMARY KEY
measure	text	PRIMARY KEY
value	double precision

country_group

Column	Type	Constraints
name	text	PRIMARY KEY

country_group_belonging

Column	Type	Constraints
c_group	text	PRIMARY KEY, REFERENCES country_group
country	char(3)	PRIMARY KEY, REFERENCES country
entry_year	integer	PRIMARY KEY
exit_year	integer

product

Column	Type	Constraints
hs_code	text	PRIMARY KEY
description	text

transaction

Column	Type	Constraints
product	text	PRIMARY KEY, REFERENCES product
importer	char(3)	PRIMARY KEY, REFERENCES country
exporter	char(3)	PRIMARY KEY, REFERENCES country
year	int	PRIMARY KEY
exp_val	double precision
imp_val	double precision

Complexity indexes API

Set of function to economic complexity index (ECI) and product complexity index (PCI) calculation.

Functions:

• countries_eci
• countries_pci
• countries_eci_pci

• continents_eci
• continents_pci
• continents_eci_pci

• groups_eci
• groups_pci
• groups_eci_pci

• common_eci
• common_pci
• common_eci_pci

Arguments

Required

Column	Type	Constraints
groups	text[]	Array of countries or aggregations.
start_year	integer	Year to calculate the index, or first year of the index calculation interval.

Optional

Name	Type	Default	Description
end_year	integer	0	If defined, last year of the index calculation interval.
hs_digit_pairs	integer	3	Number of HS digit pairs to be considered. Must be 1, 2 or 3.

Prefixes

countries: The argument groups contains a set of codes of the country to be considered at indexes calculation.

continents: The argument groups contains a set of codes of the continents to be considered at indexes calculation, with the transactional values of the countries belonging to each continent aggregated.

groups: The argument groups contains a set of group of country_group names to be considered at index calculation. The calculation takes in account the entry and exit of countries to the group during the time interval and a same country may be in more than one group.

common: Functions with this prefix exceptionally have group argument with cgroup[] type. Here, group is a representation of an arbitrary named group, with the respective countries list. One country can't belong to more than one group and each group members are static for the calculation interval. cgroup is defined as:

cgroup = (name text, members text[])

Return and suffixes

eci: Produces a tuple set with format (name text, eci double precision), name is an country or continent code or an group name.

pci: Produces a tuple set with format (product text, pci double precision).

eci_pci: Produces a tuple with format ((name text, eci double precision)[], (product text, pci double precision)), with contains two vectors, with are the ECI and PCI respective result vectors. The functions take virtually the same time no matter the suffix, therefore functions with eci_pci suffix are indicated to optimize situations where both measures are wanted.

Sample Usage

All countries ECI in 1998:

SELECT * FROM countries_eci(array(SELECT code FROM country), 1998);

Continent aggregated PCI, temporally aggregated between 2000 and 2006:

SELECT * FROM continents_pci(array(SELECT code FROM continent), 2000, 2006);

ECI and PCI aggregated by country code first letter and two digit pairs HS product code in 2015:

SELECT * FROM
	common_eci_pci(
	array(
		SELECT
			(left(code, 1), array_agg(code))::cgroup
			FROM
				country
			GROUP BY
				left(code, 1)
	),
	2015,
	hs_digit_pairs => 2
	);

euclidean_distance function

Calculate the distance between two transaction temporal series with different product and interval aggregations. The function returns a double precision value.

Arguments

Required

Column	Type	Constraints
country_1	text or text[]	Country 1.
country_2	text or text[]	Country 2.

Optional

Name	Type	Default	Description
start_year	integer	0	Define a inferior limit for the interval (limit included).
end_year	integer	0	Define a superior limit for the interval (limit included).
hs_code	varchar(6)	''	Product or by HS aggregation.

Sample usage

Euclidean distance between two country series:

SELECT euclidean_distance('chn', 'eua');

Euclidean distance between a country and two other countries, from the beginning of time up to 1980:

SELECT euclidean_distance('chn', ARRAY['bra', 'chl'], end_year => 1980);

Euclidean distance between two country series, from 1980 up to 1999, for products inside coffee, tea, mate e spices category:

SELECT euclidean_distance('chn', 'eua', 1980, 1999, '09');