Tengo dos tablas en la base de datos MariaDB que utilicé para mi tesis, una con años de compañías financieras y otra con datos de patentes. La tabla de datos de patentes (Tabla A) se parece a

    Company | Publication_number | Application_Date | Document_Type | Country_Code | ...
    A       | US2019xxxx         | 2018-12-01       | application   | US           | ...
    A       | WO2018xxxx         | 2018-12-01       | application   | WO           | ...
    A       | US2018xxxx         | 2017-09-23       | application   | WO           | ...
    B       | EP3285xxxx         | 2018-09-23       | patent        | EP           | ...
    B       | US2019xxxx         | 2019-01-27       | patent        | EP           | ...
    ...

La tabla con años financieros (Tabla B) se ve así:

    Company | Financial_Year | Financial_Year_Start | Financial_Year_End
    A       | 2018           | 2018-01-01           | 2018-12-31
    A       | 2017           | 2017-01-01           | 2017-12-31
    B       | 2018           | 2018-04-01           | 2019-03-31
    ...

Lo que me gustaría tener es una tabla (Tabla C) como:

    Company | Publication number | Application Date | Financial Year | Document Type | Country Code | ...
    A       | US2019xxxx         | 2018-12-01       | 2018           | application   | US           | ...
    A       | WO2018xxxx         | 2018-12-01       | 2018           | application   | WO           | ...
    A       | US2016xxxx         | 2017-09-23       | 2017           | application   | WO           | ...
    B       | EP3285xxxx         | 2018-09-23       | 2018           | patent        | EP           | ...
    B       | US2019xxxx         | 2019-01-27       | 2018           | patent        | EP           | ...
    ...

Parece bastante fácil, pero no he encontrado una manera de asignar el año financiero correctamente usando las fechas de inicio y finalización. Lamentablemente, los años financieros no siempre coinciden con los años del calendario. ¿Alguien puede decirme cómo abordar este problema? Tengo cientos de miles de documentos de patentes en mi tabla, por lo que asignar manualmente los ejercicios no es una opción.

El objetivo final es tener una tabla estadística básica derivada que se vea así (Tabla D):

    Company | Financial Year | Count US Applications | Count US patents | Count EP Applications | ...
    A       | 2018           | 89                    | 12               | 56                    | ...
    A       | 2017           | 93                    | 26               | 64                    | ...
    B       | 2018           | 53                    | 5                | 49                    | ...

Muchas gracias.

0
nhriedel 3 may. 2020 a las 08:53

2 respuestas

Aquí está la demo (en PostgreSQL) pero puede aplicar lo mismo en MariaDB .

select
    p.Company,
    Publication_number,
    Application_Date,
    Financial_Year,
    Document_Type,
    Country_Code
from patent p
join financial_years fy
on p.Company = fy.Company
and Application_Date between
    Financial_Year_Start and Financial_Year_End

Y para su resultado final puede hacer algo como esto con la declaración case

select
    Company,
    Financial_Year,
    sum(case when Country_Code = 'US' and Document_Type = 'patent' then 1 else 0 end) as 'Count US patents',
    sum(case when Country_Code = 'US' and Document_Type = 'application' then 1 else 0 end) as 'Count US applications',
    sum(case when Country_Code = 'EP' and Document_Type = 'patent' then 1 else 0 end) as 'Count EP patents',
    sum(case when Country_Code = 'EP' and Document_Type = 'application' then 1 else 0 end) as 'Count RP applications'
from yourTable
group by
    Company,
    Financial_Year
order by
    Financial_Year
1
zealous 3 may. 2020 a las 06:34

Las tablas están relacionadas por compañía y rango de fechas, así que únete a ellas. Esto simplemente debería ser:

select *
from a
join b on a.company = b.company
      and a.application_date between b.financial_year_start and b.financial_year_end;
1
Thorsten Kettner 3 may. 2020 a las 06:03