0

How can I realize my purpose there are ten of thousands of numbers like CN201510747352 in a Excel document.

SELECT 
  p.application_number AS app, 
  COUNT(DISTINCT c.publication_number) Citations 
FROM `patents-public-data.patents.publications` AS p,
UNNEST(citation) c
WHERE p.application_number **IN ('CN-201510747352-A')** 
GROUP BY p.application_number

I want search tens of thousands of data like CN-201510747352-A, is there any convenient ways to realize it?

Daniel Larsson
  • 527
  • 2
  • 6
  • 23
LEBRON L
  • 17
  • 6

1 Answers1

0

  1. Import your Excel spreadsheet into Google sheets.

  2. Create a federated BigQuery table pointing to your Google sheet:

enter image description here

  1. Now you can write queries that SELECT * FROM `patents-public-data.patents.publications, while filtering from your sheet with WHERE p.application_number IN (SELECT app_n FROM `my.sheet`).
Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325
  • How do I open the Create Table page? Could you send me a link? – LEBRON L Nov 28 '18 at 01:03
  • Would you mind sending me a detail solution? I'm a newbie here, so I cannot understand the answer. Thank you for your help! – LEBRON L Nov 28 '18 at 01:47
  • I don't know the third step and what should be filled in location in the second step? – LEBRON L Nov 28 '18 at 02:24
  • Query Failed Error: Encountered " "FROM" "FROM "" at line 1, column 10. Was expecting: Job ID: independent-tea-223707:US.bquijob_6840ba32_1675838bbf9 SQL 1 SELECT * FROM `patents-public-data.patents.publications – LEBRON L Nov 28 '18 at 02:50
  • Could you give me a detail instruction to realize it? – LEBRON L Nov 28 '18 at 02:58
  • Error: Invalid table name: `Mypatent.myappnum` [Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)] – LEBRON L Nov 28 '18 at 05:33
  • It didn't work out. Would you mind sending me a complementary SQL order to realize it? – LEBRON L Nov 28 '18 at 07:03