I would like to add a couple of items on Elizabeths' answer (which is correct).
The OECD document is from 2014; IPC and CPC codes migh have changed. But the Y-tags in the CPC scheme have absolutely changed and have been pruned. So you will also need to check if those Y-tags are still valid or maybe need to be changed or replaced by other classification(s).
In your SQL, you join tls201_appln with the IPC and CPC table although you don't use the CPC table neither CPC data in your WHERE or SELECT clause. So I would assume you don't need it. But if you join to both tables, the data base needs to have matching data in your WHERE clause for both tables. If there is no CPC data (whatever code), the application would not be taken into account.
One also need to be careful when using IPC and CPC codes.
i.ipc_class_symbol = '
H05K 13/04' is valid, and there are no subgroups.
You can observe that when you run the query below.
Code: Select all
SELECT distinct ipc_class_symbol
FROM tls209_appln_ipc
where ipc_class_symbol LIKE 'H05K 13/04%'
-- observer the % wildcard with the LIKE statement
But if you run the same query on the CPC table, you will see that there are plenty of subsgroups in "H05K 13/04"; and you will need to use the LIKE statement to be sure you get all of them.
https://worldwide.espacenet.com/patent/ ... =H05K13/04
Code: Select all
SELECT distinct cpc_class_symbol
FROM tls224_appln_cpc
where cpc_class_symbol LIKE 'H05K 13/04%'
And similar for CPC "B01D 53/34"
Code: Select all
SELECT distinct cpc_class_symbol
FROM tls224_appln_cpc
where cpc_class_symbol LIKE 'B01D 53/34%'
But
BEWARE of CPC "B01D 53/34". As CPC code, it has subgroups that do not start with "B01D 53/34"
See:
https://worldwide.espacenet.com/patent/ ... =B01D53/34
ESPACENET has an elegant solution for this problem via the "/LOW" operator. I allows you to select all the codes
(subgroups) when you tick "B01D53/34". But in SQL you will need to be sure that all the subgroups are included in your query structure via "ORs" or "LIKE"; that means, checking them manually and adding them to your WHERE clause.
So taking all the above into account, have a look at this:
Code: Select all
SELECT appln_filing_year, count (distinct (a.appln_id)) as 'number of application'
--distinct to avoid double counts when an application has >1 CN inventor or applicant,
-- only single applications are counted.
FROM tls201_appln a
join tls207_pers_appln x on a.appln_id = x.appln_id
join tls206_person pers on x.person_id = pers.person_id
WHERE
(
a.appln_id in (Select appln_id from tls209_appln_ipc where ipc_class_symbol = 'H05K 13/04'
or ipc_class_symbol in('B01D 53/34', 'B01D 53/38') or ipc_class_symbol like 'B01D 53/4%'or ipc_class_symbol like 'B01D 53/5%' or ipc_class_symbol like 'B01D 53/6%'
or ipc_class_symbol like 'B01D 53/7%' or ipc_class_symbol like 'B01D 53/8%' or ipc_class_symbol like 'B01D 53/9%')
or
a.appln_id in (Select appln_id from tls224_appln_cpc where cpc_class_symbol LIKE 'H05K 13/04%'
or cpc_class_symbol like 'B01D 53/34' or cpc_class_symbol = 'B01D 53/38' or cpc_class_symbol like 'B01D 53/4%'or cpc_class_symbol like 'B01D 53/5%' or cpc_class_symbol like 'B01D 53/6%'
or cpc_class_symbol like 'B01D 53/7%' or cpc_class_symbol like 'B01D 53/8%' or cpc_class_symbol like 'B01D 53/9%' )
)
AND a.appln_filing_year BETWEEN 2000 AND 2019
and person_ctry_code ='CN'
GROUP BY a.appln_filing_year
ORDER BY a.appln_filing_year asc