Hello Wei,
A couple of observations:
In your query, you tried to count at the same time the forward and the backward citations. If one would want to do this, the query would need to have 2 joins via tls212, one to the forward citations and one to the backward citations. I feel this is needlessly complicating the data aggregation.
I would separate forward and backward citations and then use further analysis to bring the result together if there would be any need for that.
You used LEFT JOINS, which I personally try to avoid if there is no specific need for it. Assuming that you only want to look at applications that effectively have citations and an IPC code, this is not needed.
Your condition "and i.ipc_position = 'F'" will only have an effect for US applications, which in your query is indeed the case through the "and p.publn_auth = 'US'" conditions. But keep in mind that you will exclude all applications that have a H02M classifications NOT in the first position. (this amounts to about 30.000 out of 40.000 applications - is this really what you want ?)
And a last remark: the query specifies " count(distinct(c.citn_id)) ". Counting citn_id's will include NPL for the backward citations, but there are no forward citations to NPL. Unless you have a specific need to do so, I would only count citations to patent publications by forcing c.cited_pat_publn_id <> 0
Keeping the above in mind; here is a query for a backward citations count :
Code: Select all
select a.appln_id, a.appln_filing_date, p.publn_auth, p.publn_nr, p.publn_date,
p.publn_claims,i.ipc_class_symbol, count(c.cited_pat_publn_id) as bcitation
from tls201_appln a
join tls209_appln_ipc i on a.appln_id = i.appln_id
join tls211_pat_publn p on p.appln_id = a.appln_id
join tls212_citation c on p.pat_publn_id = c.pat_publn_id
where i.ipc_class_symbol like 'H02M%'
and p.publn_auth = 'US'
and p.publn_first_grant = 'Y'
and a.granted = 'Y'
and i.ipc_position = 'F'
AND a.earliest_publn_year BETWEEN 2005 AND 2017
and c.cited_pat_publn_id <> 0
group by a.appln_id, a.appln_filing_date, p.publn_nr, p.publn_date,
p.publn_claims, p.publn_auth , i.ipc_class_symbol
order by a.appln_id
Here is a query that gives the individual backward citations (101 in total) for the first publication of the above query (US7881084):
Code: Select all
SELECT citing.appln_id, citing.publn_auth, citing.publn_nr, citing.publn_kind, citing.publn_date, tls212_citation.*,
cited.publn_auth, cited.publn_nr, cited.publn_kind, cited.publn_date,tls215_citn_categ.citn_categ, npl_biblio
FROM tls211_pat_publn citing join tls212_citation on citing.pat_publn_id = tls212_citation.pat_publn_id
join tls211_pat_publn cited on tls212_citation.cited_pat_publn_id = cited.pat_publn_id
left join tls215_citn_categ on tls212_citation.pat_publn_id = tls215_citn_categ.pat_publn_id and tls212_citation.citn_id = tls215_citn_categ.citn_id
--left join needed here otherwise only citations from the search report are included in the result, and not for example citations during opposition.
join tls214_npl_publn on tls212_citation.cited_npl_publn_id = tls214_npl_publn.npl_publn_id
where citing.appln_id in (463368) and citing.publn_kind = 'B2' and cited_pat_publn_id <> 0
order by citing.publn_date desc, citing.appln_id
And here is the query that looks at the forward citations: (observe the change in the way the tls212 table is joined to the publication, and that we now count pat_publn_id's !)
Code: Select all
select a.appln_id, a.appln_filing_date, p.publn_auth, p.publn_nr, p.publn_date,
p.publn_claims,i.ipc_class_symbol, count(distinct (c.pat_publn_id)) as fcitation
from tls201_appln a
join tls209_appln_ipc i on a.appln_id = i.appln_id
join tls211_pat_publn p on p.appln_id = a.appln_id
join tls212_citation c on p.pat_publn_id = c.cited_pat_publn_id
where i.ipc_class_symbol like 'H02M%'
and p.publn_auth = 'US'
and p.publn_first_grant = 'Y'
and a.granted = 'Y'
and i.ipc_position = 'F'
AND a.earliest_publn_year BETWEEN 2005 AND 2017
and c.cited_pat_publn_id <> 0
group by a.appln_id, a.appln_filing_date, p.publn_nr, p.publn_date,
p.publn_claims, p.publn_auth , i.ipc_class_symbol
order by a.appln_id
And here is a query that shows all the citing publications (9) for the first application resulted from the previous query (US7881084):
Code: Select all
select a.appln_id, a.appln_filing_date, p.publn_auth, p.publn_nr, p.publn_date,
p.publn_claims,i.ipc_class_symbol, citing.*
from tls201_appln a
join tls209_appln_ipc i on a.appln_id = i.appln_id
join tls211_pat_publn p on p.appln_id = a.appln_id
join tls212_citation c on p.pat_publn_id = c.cited_pat_publn_id
join tls211_pat_publn citing on c.pat_publn_id = citing.pat_publn_id
where i.ipc_class_symbol like 'H02M%'
and p.publn_auth = 'US'
and p.publn_first_grant = 'Y'
and a.granted = 'Y'
and i.ipc_position = 'F'
AND a.earliest_publn_year BETWEEN 2005 AND 2017
and c.cited_pat_publn_id <> 0
and a.appln_id = 463368
order by a.appln_id