...
| Info |
|---|
|
How many US documents published on 20161004 have updated CPC classifications? | Code Block |
|---|
| select count(*) as docs_with_modified_cpc
from xml.t_patent_document_values as a
inner join xml.t_classifications_cpc as b
on (a.publication_id=b.publication_id)
where a.country='US'
and a.published='2016-10-04'
and a.deleted_load_id is null -- disregard deleted documents
and a.withdraw != true -- disregard withdrawn documents
/*
If b.modified_load_id > a.created_load_id,
the table has been touched and we can therefore
assume CPC values have been modified
*/
and b.modified_load_id > a.created_load_id;
docs_with_modified_cpc
------------------------
6145
|
Out of 6680 documents published on 20161004, 6145 had CPC modifications after publication. |
...
The answers to these questions are important for a variety of reasons, most importantly for down-stream processing. In the case of CLAIMS Direct primary data warehouses, we trigger indexing based on the answer to these questions. The first thing to look at is what happened on a certain date, for the sake of an example, we will use 20161004.
| Code Block |
|---|
|
select load_source, count(*)
from reporting.t_client_load_process
where date_trunc('day', completed_stamp) = '2016-10-04'::date
group by load_source
order by load_source;
load_source | count
--------------+-------
DOCDB | 7
IFI-CLAIMINF | 1
IFI-Cont | 15
IPCR | 47
US | 1
USPTO-OG | 1
USREAS | 1 |
How many documents were added to the data warehouse?
| Code Block |
|---|
|
select count(*) as new_docs
from xml.t_patent_document_values
where created_load_id in (
select load_id
from reporting.t_client_load_process
where date_trunc('day', completed_stamp) = '2016-10-04'::date
);
new_docs
----------
6680 |
... and which documents were updated?
| Code Block |
|---|
|
select count(*) as updated_docs
from xml.t_patent_document_values
where created_load_id < modified_load_id
and modified_load_id in (
select load_id
from reporting.t_client_load_process
where date_trunc('day', completed_stamp) = '2016-10-04'::date
);
updated_docs
----------
362388 |
...