Trying to count patents by IPC 4 digit code

Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
Post Reply

jfl4bb
Posts: 1
Joined: Sat Sep 04, 2021 4:58 am

Trying to count patents by IPC 4 digit code

Post by jfl4bb » Sat Sep 04, 2021 5:07 am

The method I plan to use to count PATENTS by 4 digit IPC is to count them fractionally. So if A01C is mentioned 3 times for a patent application out of 9 total citations, then it counts as 4/9ths of a patent. Then sum by IPC to get total for each year.

Current I have this code which gives, for each patent application, the number of times each 4 digit classification is used:

SELECT appln_filing_year, SUBSTRING(ipc_class_symbol, 1, 4) AS ipc,
COUNT(SUBSTRING(ipc_class_symbol, 1, 4)) AS techcount, tls209_appln_ipc.appln_id
FROM tls209_appln_ipc JOIN tls201_appln ON tls209_appln_ipc.appln_id = tls201_appln.appln_id
WHERE appln_auth = 'US' AND appln_filing_year = 2009
GROUP BY appln_filing_year, tls209_appln_ipc.appln_id, appln_auth, SUBSTRING(ipc_class_symbol, 1, 4)
ORDER BY tls209_appln_ipc.appln_id

Then I have this query which gives the number of total tech classes on each application:
SELECT tls209_appln_ipc.appln_id, COUNT( tls209_appln_ipc.appln_id)
FROM tls201_appln JOIN tls209_appln_ipc ON tls209_appln_ipc.appln_id = tls201_appln.appln_id
WHERE appln_auth = 'US' AND appln_filing_year = 2009
GROUP BY tls209_appln_ipc.appln_id
ORDER BY tls209_appln_ipc.appln_id

However, I have been unable to find a way to combine these where I can, for each patent application/IPC class combination, divide the first query value by the second query value. Can anyone assist? (Open to suggestions on easier ways to accomplish the end goal as well)


MartinK
Posts: 3
Joined: Tue Jun 29, 2021 7:39 am

Re: Trying to count patents by IPC 4 digit code

Post by MartinK » Wed Sep 15, 2021 6:18 pm

Does this help?:

Code: Select all

SELECT appln_filing_year, SUBSTRING(ipc_class_symbol, 1, 4) AS ipc,
COUNT(SUBSTRING(ipc_class_symbol, 1, 4)) AS techcount, 
COUNT(SUBSTRING(ipc_class_symbol, 1, 4)) * fraction AS weightedIPC,
i.appln_id
FROM tls209_appln_ipc i 
JOIN tls201_appln a ON i.appln_id =a.appln_id

join 
	(select appln_id, 1.0 / COUNT(*) as fraction
	from tls209_appln_ipc
   	group by appln_id
 	) t   -- create a temporary table and call it "t"
 	on i.appln_id = t.appln_id
	
WHERE appln_auth = 'US' AND appln_filing_year = 2009
GROUP BY appln_filing_year, i.appln_id, appln_auth, SUBSTRING(ipc_class_symbol, 1, 4), fraction
ORDER BY i.appln_id


Post Reply