Acquire lots of citation data from BigQuery by application












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?










share|improve this question





























    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?










    share|improve this question



























      0












      0








      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?










      share|improve this question
















      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?







      sql google-bigquery google-patent-search






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Nov 27 '18 at 7:42









      Daniel Larsson

      5372419




      5372419










      asked Nov 27 '18 at 7:38









      LEBRON LLEBRON L

      76




      76
























          1 Answer
          1






          active

          oldest

          votes


















          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`).






          share|improve this answer
























          • How do I open the Create Table page? Could you send me a link?

            – LEBRON L
            Nov 28 '18 at 1:03











          • cloud.google.com/bigquery/docs/tables#create-table, switch to web ui

            – Felipe Hoffa
            Nov 28 '18 at 1:18











          • 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 1:47











          • i.imgur.com/zBYlWRA.png

            – Felipe Hoffa
            Nov 28 '18 at 1:59











          • I don't know the third step and what should be filled in location in the second step?

            – LEBRON L
            Nov 28 '18 at 2:24











          Your Answer






          StackExchange.ifUsing("editor", function () {
          StackExchange.using("externalEditor", function () {
          StackExchange.using("snippets", function () {
          StackExchange.snippets.init();
          });
          });
          }, "code-snippets");

          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "1"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: true,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: 10,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53494831%2facquire-lots-of-citation-data-from-bigquery-by-application%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes









          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`).






          share|improve this answer
























          • How do I open the Create Table page? Could you send me a link?

            – LEBRON L
            Nov 28 '18 at 1:03











          • cloud.google.com/bigquery/docs/tables#create-table, switch to web ui

            – Felipe Hoffa
            Nov 28 '18 at 1:18











          • 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 1:47











          • i.imgur.com/zBYlWRA.png

            – Felipe Hoffa
            Nov 28 '18 at 1:59











          • I don't know the third step and what should be filled in location in the second step?

            – LEBRON L
            Nov 28 '18 at 2:24
















          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`).






          share|improve this answer
























          • How do I open the Create Table page? Could you send me a link?

            – LEBRON L
            Nov 28 '18 at 1:03











          • cloud.google.com/bigquery/docs/tables#create-table, switch to web ui

            – Felipe Hoffa
            Nov 28 '18 at 1:18











          • 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 1:47











          • i.imgur.com/zBYlWRA.png

            – Felipe Hoffa
            Nov 28 '18 at 1:59











          • I don't know the third step and what should be filled in location in the second step?

            – LEBRON L
            Nov 28 '18 at 2:24














          0












          0








          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`).






          share|improve this answer














          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`).







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Nov 27 '18 at 8:24









          Felipe HoffaFelipe Hoffa

          21.8k252114




          21.8k252114













          • How do I open the Create Table page? Could you send me a link?

            – LEBRON L
            Nov 28 '18 at 1:03











          • cloud.google.com/bigquery/docs/tables#create-table, switch to web ui

            – Felipe Hoffa
            Nov 28 '18 at 1:18











          • 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 1:47











          • i.imgur.com/zBYlWRA.png

            – Felipe Hoffa
            Nov 28 '18 at 1:59











          • I don't know the third step and what should be filled in location in the second step?

            – LEBRON L
            Nov 28 '18 at 2:24



















          • How do I open the Create Table page? Could you send me a link?

            – LEBRON L
            Nov 28 '18 at 1:03











          • cloud.google.com/bigquery/docs/tables#create-table, switch to web ui

            – Felipe Hoffa
            Nov 28 '18 at 1:18











          • 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 1:47











          • i.imgur.com/zBYlWRA.png

            – Felipe Hoffa
            Nov 28 '18 at 1:59











          • I don't know the third step and what should be filled in location in the second step?

            – LEBRON L
            Nov 28 '18 at 2:24

















          How do I open the Create Table page? Could you send me a link?

          – LEBRON L
          Nov 28 '18 at 1:03





          How do I open the Create Table page? Could you send me a link?

          – LEBRON L
          Nov 28 '18 at 1:03













          cloud.google.com/bigquery/docs/tables#create-table, switch to web ui

          – Felipe Hoffa
          Nov 28 '18 at 1:18





          cloud.google.com/bigquery/docs/tables#create-table, switch to web ui

          – Felipe Hoffa
          Nov 28 '18 at 1:18













          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 1:47





          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 1:47













          i.imgur.com/zBYlWRA.png

          – Felipe Hoffa
          Nov 28 '18 at 1:59





          i.imgur.com/zBYlWRA.png

          – Felipe Hoffa
          Nov 28 '18 at 1:59













          I don't know the third step and what should be filled in location in the second step?

          – LEBRON L
          Nov 28 '18 at 2:24





          I don't know the third step and what should be filled in location in the second step?

          – LEBRON L
          Nov 28 '18 at 2:24




















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Stack Overflow!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53494831%2facquire-lots-of-citation-data-from-bigquery-by-application%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          A CLEAN and SIMPLE way to add appendices to Table of Contents and bookmarks

          Calculate evaluation metrics using cross_val_predict sklearn

          Insert data from modal to MySQL (multiple modal on website)