谷歌专利bigquery转发引用

Question

我想要一份专利清单(发表编号、提交日期等)该报告列举了2003至2015年间提交的所有美国专利。不确定哪里出了问题，但我当前的代码(如下)没有返回任何结果。有人能帮我修改一下代码吗？

SELECT c.publication_number AS Pub, c.category AS Cat, c.filing_date AS Date, p.publication_number AS Citedby, p.filing_date AS Cited_Date 
FROM `patents-public-data.patents.publications` AS p, UNNEST(citation) AS c 
WHERE c.publication_number LIKE ('US%') AND (c.filing_date BETWEEN 20030101 AND 20041231);

非常感谢你的帮助。

Answer 1

之所以会发生这种情况，是因为citation.filing_date的每一行都有0值。查看JSON输出文件和Date字段：

[
  {
    "Citedby": "US-2014313399-A1",
    "Cited_Date": "20140630",
    "Pub": "US-8792047-B2",
    "Cat": "PRS",
    "Date": "0"
  },
  {
    "Citedby": "US-2013120526-A1",
    "Cited_Date": "20120731",
    "Pub": "US-2011134210-A1",
    "Cat": "PRS",
    "Date": "0"
  },
  {
    "Citedby": "US-2013242999-A1",
    "Cited_Date": "20120530",
    "Pub": "US-7616646-B1",
    "Cat": "PRS",
    "Date": "0"
  },

您不应该在WHERE子句中使用(c.filing_date BETWEEN 20030101 AND 20041231)：

SELECT p.publication_number AS Citedby, p.filing_date AS Cited_Date, c.publication_number AS Pub, c.category AS Cat, c.filing_date AS Date
FROM `patents-public-data.patents.publications` AS p, UNNEST(citation) AS c 
WHERE c.publication_number LIKE ('US%');

它打印出专利列表。或者您可以添加(p.filing_date BETWEEN 20030101 AND 20151231)，但请记住这是publications.filing_date而不是citation.filing_date。

我希望它能有所帮助。