CODEWITHSUNDEEP
sqlgoogle-bigquerygoogle-patent-search
LEBRON L
LEBRON L

Reputation: 17

Make the outcome contains application number or query application number by publication number in outoutcome

SELECT 
  c.publication_number AS Pub, 
  COUNT(DISTINCT REGEXP_EXTRACT(p.publication_number, r'(.+-.+)-')) AS CitedByCount
FROM `patents-public-data.patents.publications` AS p, 
UNNEST(citation) AS c 
WHERE c.publication_number IN (
  SELECT publication_number 
  FROM `patents-public-data.patents.publications`
  WHERE application_number IN ('CN-201510747352-A') 
)
GROUP BY c.publication_number

I can use application number to acquire the CitedBy, but the outcome is Pub and CitedBy. How can I realize the purpose that the outcome contains application number and CitedBy.

Upvotes: 0

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270723

Perhaps you don't want the group by?

select c.publication_number as Pub, 
       regexp_extract(p.publication_number, r'(.+-.+)-')) as cited_by
from `patents-public-data.patents.publications` p cross join 
     unnest(citation) c 
where c.publication_number in (select publication_number 
                               from `patents-public-data.patents.publications`
                               where application_number in ('CN-201510747352-A') 
                              );

Alternatively, you can aggregate them into an array:

select c.publication_number as Pub, 
       array_agg(distinct regexp_extract(p.publication_number, r'(.+-.+)-'))) as cited_bys
from `patents-public-data.patents.publications` p cross join 
     unnest(citation) c 
where c.publication_number in (select publication_number 
                               from `patents-public-data.patents.publications`
                               where application_number in ('CN-201510747352-A') 
                              )
group by c.publication_number;

Upvotes: 1

Related Questions