Le brevet le plus cité au monde

introduction

Depuis que les vacances ont commencé, je voulais faire une analyse urgente, alors je me suis demandé quel document de brevet avait le plus grand nombre de citations au monde.

SQL dans BigQuery

Pour la première fois dans l'apprentissage en ligne, j'ai appris que SQL est prononcé comme grinçant. ..

La difficulté était de savoir comment créer un nombre total de citations et de déduplication (en excluant le fait d'être utilisé plusieurs fois pour des raisons de refus de la même application) et comment créer un modèle de nombre de citations, et comme BigQuery ne contient que des informations de citation, il est cité La partie qui génère des informations (sens inverse). C'est un peu compliqué, mais cela ressemble à ce qui suit.

<détails>

SQL pour la population </ résumé>

WITH bibtable as (
  SELECT
    pub.application_number AS appnum,
    pub.publication_number AS pubnum,
    pub.filing_date as appday,
    STRING_AGG(DISTINCT(applicants.name)) AS applicants ,
    SUBSTR(STRING_AGG(ipcs.code),0,1) AS ipc4,
    STRING_AGG(DISTINCT(title.text)) AS title
  FROM `patents-public-data.patents.publications_201912` AS pub,
     UNNEST(title_localized) AS title,
     UNNEST(assignee_harmonized) as applicants,
     UNNEST(ipc) as ipcs
     GROUP BY appnum,pubnum,appday
)

SELECT 
  pubnum,
  SUBSTR(pubnum,0,2) AS appcountry,
  SUBSTR(STRING_AGG(DISTINCT(CAST(bibtable.appday AS STRING))),0,4) AS appyear,
  COUNT(main.application_number) AS total_cit_count,
  COUNT(DISTINCT(main.family_id)) AS unique_cit_count,
  STRING_AGG(DISTINCT(ipc4)) AS ipcs,
  STRING_AGG(DISTINCT(title)) AS titles,
  STRING_AGG(DISTINCT(applicants)) AS applicants,
  SUM(CASE SUBSTR(ipcs.code,0,1) WHEN 'A' THEN 1 ELSE 0 END) as IPC_A,
  SUM(CASE SUBSTR(ipcs.code,0,1) WHEN 'B' THEN 1 ELSE 0 END) as IPC_B,
  SUM(CASE SUBSTR(ipcs.code,0,1) WHEN 'C' THEN 1 ELSE 0 END) as IPC_C,
  SUM(CASE SUBSTR(ipcs.code,0,1) WHEN 'D' THEN 1 ELSE 0 END) as IPC_D,
  SUM(CASE SUBSTR(ipcs.code,0,1) WHEN 'E' THEN 1 ELSE 0 END) as IPC_E,
  SUM(CASE SUBSTR(ipcs.code,0,1) WHEN 'F' THEN 1 ELSE 0 END) as IPC_F,
  SUM(CASE SUBSTR(ipcs.code,0,1) WHEN 'G' THEN 1 ELSE 0 END) as IPC_G,
  SUM(CASE SUBSTR(ipcs.code,0,1) WHEN 'H' THEN 1 ELSE 0 END) as IPC_H,
  SUM(CASE SUBSTR(main.publication_number,0,2) WHEN 'US' THEN 1 ELSE 0 END) as US,
  SUM(CASE SUBSTR(main.publication_number,0,2) WHEN 'JP' THEN 1 ELSE 0 END) as JP,
  SUM(CASE SUBSTR(main.publication_number,0,2) WHEN 'EP' THEN 1 ELSE 0 END) as EP,
  SUM(CASE SUBSTR(main.publication_number,0,2) WHEN 'KR' THEN 1 ELSE 0 END) as KR,
  SUM(CASE SUBSTR(main.publication_number,0,2) WHEN 'CN' THEN 1 ELSE 0 END) as CN,
  SUM(CASE SUBSTR(main.publication_number,0,2) WHEN 'WO' THEN 1 ELSE 0 END) as WO
FROM 
  `patents-public-data.patents.publications_201912` as main,
  UNNEST(main.citation) AS cit,
  UNNEST(main.ipc) AS ipcs
LEFT JOIN bibtable
  ON cit.publication_number = bibtable.pubnum
WHERE SUBSTR(main.publication_number,0,2) IN ('US','JP','EP','CN','KR','WO') AND pubnum IS NOT NULL AND ipcs.first = TRUE
GROUP BY appnum,pubnum
ORDER BY total_cit_count DESC,pubnum DESC

Version spécifique à SQL pour la population: ARRAY_AGG et STRUCT peuvent être plus propres

WITH bibtable as (
  SELECT
    pub.application_number AS appnum,
    pub.publication_number AS pubnum,
    pub.filing_date as appday,
    STRING_AGG(DISTINCT(applicants.name)) AS applicants ,
    STRING_AGG(DISTINCT(title.text)) AS texts,
    SUBSTR(STRING_AGG(ipcs.code),0,1) AS ipc4,
    STRING_AGG(DISTINCT(title.text)) AS title
  FROM `patents-public-data.patents.publications_201912` AS pub,
     UNNEST(title_localized) AS title,
     UNNEST(assignee_harmonized) as applicants,
     UNNEST(ipc) as ipcs
     GROUP BY appnum,pubnum,appday
)

SELECT 
  bibtable.pubnum,
  SUBSTR(bibtable.pubnum,0,2) AS appcountry,
  SUBSTR(STRING_AGG(DISTINCT(CAST(bibtable.appday AS STRING))),0,4) AS appyear,
  #STRING_AGG(DISTINCT(cit.application_number)) as appnum,
  #STRING_AGG(DISTINCT(cit.type)) AS cit_type,
  COUNT(main.application_number) AS total_cit_count,
  COUNT(DISTINCT(main.family_id)) AS unique_cit_count,
  STRING_AGG(DISTINCT(ipc4)) AS ipcs,
  STRING_AGG(DISTINCT(title)) AS titles,
  STRING_AGG(DISTINCT(applicants)) AS applicants,
  ARRAY_AGG(STRUCT(SUBSTR(ipcs.code,0,1) AS ipc_sec,
                   SUBSTR(main.publication_number,0,2) AS cc,
                   SUBSTR(CAST(main.filing_date AS STRING),0,4) AS year,
                   appls)
            ) AS fcit,
   #STRING_AGG(cit.type),STRING_AGG(DISTINCT(family_id))
FROM 
  `patents-public-data.patents.publications_201912` as main,
  UNNEST(main.citation) AS cit,
  UNNEST(main.ipc) AS ipcs,
  UNNEST(main.assignee) AS appls
LEFT JOIN bibtable
  ON cit.publication_number = bibtable.pubnum
WHERE SUBSTR(main.publication_number,0,2) IN ('US','JP','EP','CN','KR','WO') AND pubnum IS NOT NULL AND ipcs.first = TRUE
GROUP BY appnum,pubnum
ORDER BY total_cit_count DESC,pubnum DESC

0. Résultat de sortie (10 premiers)

Le temps d'exécution est d'environ 5 minutes. Au total, 30 262 060 documents ont été extraits. Voici les 10 temps les plus cités.

"IPC_ ~" à la fin de la colonne est le nombre de sections IPC à partir desquelles le document est cité (nombre de fois), et le nom du pays est le numéro à partir duquel la demande est citée (nombre de fois).

Row	pubnum	appcountry	appyear	total_cit_count	unique_cit_count	ipcs	titles	applicants	IPC_A	IPC_B	IPC_C	IPC_D	IPC_E	IPC_F	IPC_G	IPC_H	US	JP	EP	KR	CN	WO
1	US-4683202-A	US	1985	8996	4766	C	Process for amplifying nucleic acid sequences	CETUS CORP	1602	148	6116	13	1	12	1083	21	5542	0	1701	1	1	1751
2	US-4816567-A	US	1983	8668	4748	C	Recombinant immunoglobin preparations	GENENTECH INC	3319	27	4354	8	0	3	954	3	4387	0	1878	0	1	2402
3	US-4683195-A	US	1986	7174	3763	C	Process for amplifying, detecting, and/or-cloning nucleic acid sequences	CETUS CORP	1115	154	4853	1	1	11	1021	18	4593	0	1355	1	1	1224
4	US-5223409-A	US	1991	4956	2523	C	Directed evolution of novel binding proteins	PROTEIN ENG CORP	1598	17	2833	10	0	2	493	3	2629	2	1092	1	0	1232
5	US-5523520-A	US	1994	4759	3761	C	Mutant dwarfism gene of petunia	GOLDSMITH SEEDS INC	4585	1	162	0	2	1	5	3	4759	0	0	0	0	0
6	US-4946778-A	US	1989	4657	2474	C	Single polypeptide chain binding molecules	GENEX CORP	1655	9	2436	1	0	1	554	1	2521	1	1146	1	0	988
7	US-5585089-A	US	1995	4604	2520	A	Humanized immunoglobulins	PROTEIN DESIGN LABS INC	1829	38	2306	0	0	1	430	0	2337	1	976	0	2	1288
8	US-5530101-A	US	1990	4036	1987	A	Humanized immunoglobulins	PROTEIN DESIGN LABS INC	1776	9	1920	0	0	1	330	0	2564	2	672	0	4	794
9	US-5892900-A	US	1996	4035	1944	G	Systems and methods for secure transaction management and electronic rights protection	INTERTRUST TECH CORP	53	26	3	0	1	1	2886	1065	3955	2	19	4	29	26
10	US-2003189401-A1	US	2003	3849	1958	C	Organic electroluminescent device	INT MFG & ENG SERVICES CO LTD	2	14	33	1	1	6	759	3033	3820	1	16	3	1	8

Le numéro un mondial est US4683202A "Procédé pour amplifier des séquences d'acide nucléique". Est-ce une application bio? La deuxième place est comme ça.

Enregistrez les résultats sous forme de table BigQuery. Appelez bigquery depuis pythono et essayez de visualiser le résultat avec des pandas.

résultat

1. Entier

Répartition du nombre de citations

Pour le moment, un histogramme. <détails>
code </ summary>

from google.cloud import bigquery
project_id = '~~~~~'
client = bigquery.Client(project=project_id)

#Requete
query =  """
                SELECT total_cit_count,count(*) AS fcit_count
                FROM `bqml_tutorial.fcitdata`
                GROUP BY total_cit_count
                ORDER BY total_cit_count DESC
         """
#Recevez chez les pandas.
df_count_fcit = client.query(query).to_dataframe()

#terrain

import plotly.express as px
px.histogram(df_count_fcit,x='total_cit_count')

En regardant cela, je crains qu'il n'y ait une montagne pour 3000 fois, plutôt que d'être doux dans l'ordre du nombre de citations. Cette tendance est la même dans l'histogramme du nombre de citations sans duplication (il y a une montagne pour 1800 fois).

Répartition des âges cités

Quel document d'âge a le plus de citations? Bien qu'il augmente régulièrement, il est actuellement le plus élevé autour de 2012-2013.

<détails>

code </ summary>

#La partie pour obtenir les données dans le dataframe est uniquement le changement de nom de colonne, donc il est omis.
fig = px.bar(df_count_fcit[df_count_fcit['appyear'].astype(int)>1990],x='appyear',y='count')
fig.show()

Dans quel pays l'application est la plus citée?

Les données originales incluent la citation de l'examinateur + la citation du demandeur (IDS) aux États-Unis, mais il semble que d'autres pays ne le font pas, donc je ne peux pas le dire sans condition. Les cotations chinoises devraient augmenter ces dernières années, se rapprochant du JP.

<détails>

code </ summary>

SELECT appcountry,SUM(unique_cit_count) AS count
FROM `bqml_tutorial.fcitdata`
GROUP BY appcountry
ORDER BY count DESC

fig = px.bar(df_count_fcit[0:10].sort_values(by='count'),y='appcountry',x='count',orientation='h',text='count')
fig.show()

newplot (34).png

Candidats avec de nombreuses citations

Veuillez noter que le nom n'est pas identifié. Le nombre de systèmes électriques augmente proportionnellement au nombre d'applications.

<détails>

code </ summary>

SELECT applicants,COUNT(*) AS count
FROM `bqml_tutorial.fcitdata`
GROUP BY applicants
ORDER BY count DESC

fig = px.bar(df_count_fcit[0:10].sort_values(by='count'),y='applicants',x='count',orientation='h',text='count')
fig.show()

newplot (35).png

2. Par domaine technique

Les brevets du monde entier reçoivent une [classification technique] appelée IPC (classification internationale des brevets) (https://ja.wikipedia.org/wiki/%E5%9B%BD%E9%9A%9B] % E7% 89% B9% E8% A8% B1% E5% 88% 86% E9% A1% 9E). Les demandes avec le plus grand nombre de citations sont extraites pour chaque section (et la première IPC) qui est la plus grande catégorie. Il existe des classifications étranges autres que A-H, mais c'est amusant de voir des applications dans chaque domaine.

<détails>

code </ summary>

SELECT * FROM (
  SELECT 
    ipcs,
    row_number() over (partition by ipcs order by unique_cit_count DESC) AS rank,
    pubnum,
    total_cit_count,unique_cit_count,
    applicants,
    titles
  FROM `bqml_tutorial.fcitdata`
  GROUP BY ipcs,pubnum,total_cit_count,unique_cit_count,titles,applicants
  )
WHERE rank <= 5

Presque nous <détails>

Liste </ summary>

section	rank	pubnum	total_cit_count	unique_cit_count	applicants	titles
A	1	US-5585089-A	4604	2520	PROTEIN DESIGN LABS INC	Humanized immunoglobulins
A	2	US-5530101-A	4036	1987	PROTEIN DESIGN LABS INC	Humanized immunoglobulins
A	3	US-4658085-A	2222	1829	UNIV GUELPH	Hybridization using cytoplasmic male sterility, cytoplasmic herbicide tolerance, and herbicide tolerance from nuclear genes
A	4	US-5693762-A	3455	1822	PROTEIN DESIGN LABS INC	Humanized immunoglobulins
A	5	US-5569825-A	3082	1730	GENPHARM INT	Transgenic non-human animals capable of producing heterologous antibodies of various isotypes
B	1	US-2006113549-A1	3391	1756	TOKYO INST TECH	Light-emitting device
B	2	US-2010092800-A1	3150	1678	CANON KK	Substrate for growing wurtzite type crystal and method for manufacturing the same and semiconductor device
B	3	US-6336137-B1	1505	940	SIEBEL SYSTEMS INC	Web client-server system and method for incompatible page markup and presentation languages
B	4	US-4723129-A	1489	928	CANON KK	Bubble jet recording method and apparatus in which a heating element generates bubbles in a liquid flow path to project droplets
B	5	US-6766817-B2	1856	908	TUBARC TECHNOLOGIES LLC	Fluid conduction utilizing a reversible unsaturated siphon with tubarc porosity action
C	1	US-4683202-A	8996	4766	CETUS CORP	Process for amplifying nucleic acid sequences
C	2	US-4816567-A	8668	4748	GENENTECH INC	Recombinant immunoglobin preparations
C	3	US-4683195-A	7174	3763	CETUS CORP	Process for amplifying, detecting, and/or-cloning nucleic acid sequences
C	4	US-5523520-A	4759	3761	GOLDSMITH SEEDS INC	Mutant dwarfism gene of petunia
C	5	US-5223409-A	4956	2523	PROTEIN ENG CORP	Directed evolution of novel binding proteins
D	1	US-3849241-A	1577	926	EXXON RESEARCH ENGINEERING CO	Non-woven mats by melt blowing
D	2	US-4340563-A	1491	906	KIMBERLY CLARK CO	Method for forming nonwoven webs
D	3	US-3802817-A	1282	805	ASAHI CHEMICAL IND	Apparatus for producing non-woven fleeces
D	4	US-3692618-A	1236	802	METALLGESELLSCHAFT AG	Continuous filament nonwoven web
D	5	US-4100324-A	1344	775	KIMBERLY CLARK CO	Nonwoven fabric and method of producing same
e	1	US-7174579-B1	23	13	BAUZA PEDRO	Temperature display system
E	1	US-4902508-A	1159	493	PURDUE RESEARCH FOUNDATION	Tissue graft composition
E	2	CN-201962688-U	392	391	FIRST ENGINEERING COMPANY OF CCCC FOURTH HARBOR ENGINEERING CO LTD	Diaphram wall wharf pile foundation structure,Connexion souterraine 续墙头桩桀 structure
E	3	US-5809415-A	720	352	OPENWAVE SYS INC	Method and architecture for an interactive two-way data communication network
E	4	US-5445304-A	709	312	UNITED STATES SURGICAL CORP	Safety device for a surgical stapler cartridge
E	5	US-7098794-B2	618	279	KIMBERLY CLARK CO	Deactivating a data tag for user privacy or tamper-evident packaging
F	1	US-7213940-B1	917	506	LED LIGHTING FIXTURES INC	Lighting device and lighting method
F	2	US-5037397-A	895	384	MEDICAL DISTRIBUTORS INC	Universal clamp
F	3	US-2010327766-A1	611	378	LEVINE DAVID B,RECKER MICHAEL V	Wireless emergency lighting system
F	4	US-6577073-B2	569	367	MATSUSHITA ELECTRIC IND CO LTD	Led lamp
F	5	US-6432098-B1	661	361	PROCTER & GAMBLE	Absorbent article fastening device
G	1	US-5892900-A	4035	1944	INTERTRUST TECH CORP	Systems and methods for secure transaction management and electronic rights protection
G	2	US-2007194379-A1	3592	1833	JAPAN SCIENCE & TECH AGENCY	Amorphous Oxide And Thin Film Transistor
G	3	US-5731856-A	3571	1795	SAMSUNG ELECTRONICS CO LTD	Methods for forming liquid crystal displays including thin film transistors and gate pads having a particular structure
G	4	US-2006208977-A1	3373	1746	SEMICONDUCTOR ENERGY LAB	Semiconductor device, and display device, driving method and electronic apparatus thereof
G	5	US-2009280600-A1	3163	1727	JAPAN SCIENCE & TECH AGENCY	Amorphous oxide and thin film transistor
H	1	US-2006244107-A1	3493	1844	SUGIHARA TOSHINORI,KAWASAKI MASASHI,OHNO HIDEO	Semiconductor device, manufacturing method, and electronic device
H	2	US-7674650-B2	3529	1837	SEMICONDUCTOR ENERGY LAB	Semiconductor device and manufacturing method thereof
H	3	US-2006108636-A1	3475	1777	TOKYO INST TECH	Amorphous oxide and field effect transistor
H	4	US-7061014-B2	3500	1776	JAPAN SCIENCE & TECH AGENCY	Natural-superlattice homologous single crystal thin film, method for preparation thereof, and device using said single crystal thin film
H	5	US-6294274-B1	3391	1771	TDK CORP,KAWAZOE HIROSHI
J	1	US-7693341-B2	36	25	APPLE INC	Workflows for color correcting images
J	2	US-7847532-B2	20	13	ASTEC INT LTD	Centralized controller and power manager for on-board power systems
J	3	US-7364473-B2	11	7	FUJITSU LTD	Connector for electronic device
J	4	US-7239379-B2	1	1	TECHNOLOGY INNOVATIONS LLC	Method and apparatus for determining a vertical intensity profile through a plane of focus in a confocal microscope
K	1	US-7208984-B1	18	9	LINEAR TECHN INC	CMOS driver with minimum shoot-through current
K	2	US-7287321-B2	13	7	DENSO CORP	Multi-layer board manufacturing method
K	3	US-7233174-B2	10	4	TEXAS INSTRUMENTS INC	Dual polarity, high input voltage swing comparator using MOS input transistors
K	4	US-7752749-B2	2	1	PANASONIC CORP	Electronic component mounting method and electronic component mounting device
M	1	US-8233880-B2	21	16	JOHNSON GARTH,GLOBAL TEL LINK CORP	Integration of cellular phone detection and reporting into a prison telephone system
N	1	US-8141117-B1	16	10	LOBECK MATTHEW R,ARRIS GROUP INC,CHRISTENSEN KORY D,CONINGSBY DONNA JO,BROADUS CHARLES R,KELLUM JOHN M	PC media center and extension device for interfacing with a personal video recorder through a home network
N	2	US-7258343-B2	15	9	BANDAI AMERICA INC	Card game and methods of play
N	3	US-8134758-B2	4	2	UEDA HIDENORI,NAKAISHI YOSHIAKI,OKI DATA KK	Image reading apparatus, image forming apparatus, image forming system that employs the image reading apparatus and the image forming apparatus
N	4	US-8198132-B2	2	1	GALERA MANOLITO,ALABIN LEOCADIO MORONA,FAIRCHILD SEMICONDUCTOR	Isolated stacked die semiconductor packages
O	1	US-8306908-B1	36	20	WEST CORP,PETTAY MARK J,JOHNSON ROBERT E,KEMPKES RODNEY J,BARKER THOMAS B,STRUBBE TODD B	Methods and apparatus for intelligent selection of goods and services in telephonic and electronic commerce
Q	1	US-7689487-B1	57	36	AMAZON COM INC	Computer-assisted funds transfer system
Q	2	US-8170916-B1	41	18	AHMED WAQAS,MANOLACHE FLORIN V,AMAZON TECH INC,MONGRAIN SCOTT ALLEN,MUNTEANU VALENTIN RADU,RUDEANU CORNELIU GABRIEL ALEXANDRU,WILSON AARON D,DICKER RUSSELL A,ROSCA VAL DAN DAR ION I	Related-item tag suggestions
Q	3	US-7130826-B1	5	3	IBM	Method and apparatus for conducting coinless transactions
Q	4	US-8190451-B2	2	2	DANCHA LYNNE A,TAN AGNES W H,LINDQUIST TAMMIE J,LLOYD KAREN D,GROUP HEALTH PLAN INC,KOOPMEINERS MICHAEL	Method and computer program product for predicting and minimizing future behavioral health-related hospital admissions
R	1	US-7360912-B1	42	30	PASS & SEYMOUR INC	Electrical device with lamp module
R	2	US-7196508-B2	24	15	MIRAE CORP	Handler for testing semiconductor devices
R	3	US-8052455-B1	10	7	HONGFUJIN PREC IND SHENZHEN,HON HAI PREC IND CO LTD	Mounting apparatus for flash drive
R	4	US-8128432-B2	10	5	REVOL DIDIER,LAROCHE VINCENT,FORATIER NATHALIE,JAOUEN JEAN-MARC,LEGRAND SNC,COUSY JEAN-PIERRE,LEGRAND FRANCE	Insert and method of assembling such an insert
R	5	US-8002580-B2	5	2	ANDREW LLC	Coaxial cable crimp connector
V	1	US-7692056-B2	1	1	SHANGHAI RES INST PETROCHEMICAL TECHNOLOGY SINOPEC	Process for producing lower olefins from methanol or dimethylether
Y	1	US-8471523-B2	1	1	MULTI FUNCTION CO LTD,LIN WEI-JONG	Charging/discharging device having concealed universal serial bus plug(s)

3. Par pays

Extrayez le top 5 par pays. Il est intéressant de noter qu'il existe de nombreuses différences dans chaque pays. Le Japon est tout au sujet des semi-conducteurs.

<détails>

code </ summary>

SELECT *
FROM `bqml_tutorial.fcitdata`
WHERE appcountry = 'WO'# OR appcountry = 'JP' OR appcountry = 'EP' OR appcountry = 'CN' OR appcountry = 'KR' OR appcountry = 'WO'
ORDER BY unique_cit_count DESC
LIMIT 5

<détails>

Liste </ summary>

Row	pubnum	appcountry	appyear	total_cit_count	unique_cit_count	ipcs	titles	applicants	IPC_A	IPC_B	IPC_C	IPC_D	IPC_E	IPC_F	IPC_G	IPC_H	US	JP	EP	KR	CN	WO
1	JP-2002076356-A	JP	2000	3084	1758	H	Dispositif semi-conducteur	JAPAN SCIENCE & TECH CORP	1	4	11	2	0	2	691	2373	3017	43	6	4	5	9
2	JP-2004103957-A	JP	2002	3110	1746	H	Transistor à effet de champ électrique à couche mince transparent qui utilise une couche mince homologue comme couche active,Transparent thin film field effect type transistor using homologous thin film as active layer	JAPAN SCIENCE & TECH CORP,OTA HIROMICHI	1	4	18	1	0	2	688	2396	2912	93	8	90	0	7
3	JP-2003086808-A	JP	2001	3066	1742	H	Thin film transistor and matrix display,Dispositif d'affichage à film mince et à matrice	SHARP KK,KAWASAKI MASASHI,ONO HIDEO	1	3	14	1	0	2	683	2362	2950	96	0	7	1	12
4	JP-2002289859-A	JP	2001	3060	1734	H	Couche mince,Thin-film transistor	MINOLTA CO LTD	1	3	11	1	0	2	685	2357	2979	57	2	10	6	6
5	JP-2000044236-A	JP	1998	3028	1722	H	Articles ayant une couche mince d'oxyde conducteur transparent et son procédé de fabrication,Article having transparent conductive oxide thin film and its production	HOYA CORP	1	7	20	1	0	2	680	2317	2991	13	12	3	3	6
								applicants	IPC_A	IPC_B	IPC_C	IPC_D	IPC_E	IPC_F	IPC_G	IPC_H	US	JP	EP	KR	CN	WO
1	US-4683202-A	US	1985	8996	4766	C	Process for amplifying nucleic acid sequences	CETUS CORP	1602	148	6116	13	1	12	1083	21	5542	0	1701	1	1	1751
2	US-4816567-A	US	1983	8668	4748	C	Recombinant immunoglobin preparations	GENENTECH INC	3319	27	4354	8	0	3	954	3	4387	0	1878	0	1	2402
3	US-4683195-A	US	1986	7174	3763	C	"Process for amplifying, detecting, and/or-cloning nucleic acid sequences"	CETUS CORP	1115	154	4853	1	1	11	1021	18	4593	0	1355	1	1	1224
4	US-5523520-A	US	1994	4759	3761	C	Mutant dwarfism gene of petunia	GOLDSMITH SEEDS INC	4585	1	162	0	2	1	5	3	4759	0	0	0	0	0
5	US-5223409-A	US	1991	4956	2523	C	Directed evolution of novel binding proteins	PROTEIN ENG CORP	1598	17	2833	10	0	2	493	3	2629	2	1092	1	0	1232

1	EP-2226847-A2	EP	2005	2388	1510	H	"Amorphous oxide and thin film transistor,Oxyde amorphe et transistor à couche mince,Amorpher Oxid- und Dünnschichttransistor"	JAPAN SCIENCE & TECH AGENCY	1	3	9	0	0	2	594	1779	2387	0	0	1	0	0
2	EP-0239400-A2	EP	1987	1789	1190	C	"Recombinant antibodies and methods for their production,Anticorps recombinants et leurs procédés de production,Rekombinante Antikörper und Verfahren zu deren Herstellung"	WINTER GREGORY PAUL	593	1	1066	0	0	0	129	0	572	0	490	1	2	724
3	EP-1737044-A1	EP	2005	1662	1187	H	"Oxyde amorphe et transistor	film mince,Amorph-oxid- und dünnfilmtransistor,Amorphous oxide and thin film transistor"	JAPAN SCIENCE & TECH AGENCY	0	3	7	0	0	2	452	1198	1651	0	4	0	1	6
4	EP-0404097-A2	EP	1990	1568	1152	A	"Récepteurs mono- et oligovalents, bispécifiques et oligospécifiques, ainsi que leur production et application,Bispezifische und oligospezifische, mono- und oligovalente Rezeptoren, ihre Herstellung und Verwendung,Bispecific and oligospecific, mono- and oligovalent receptors, production and applications thereof"	BEHRINGWERKE AG	505	6	910	0	0	0	147	0	262	0	495	0	0	811
5	EP-1737044-B1	EP	2005	1339	934	H	"Amorphes oxid und dünnfilmtransistor,Amorphous oxide and thin film transistor,Oxyde amorphe et transistor	film mince"	JAPAN SCIENCE & TECH AGENCY	1	0	4	1	0	0	236	1097	1339	0	0	0	0	0

1	CN-201962688-U	CN	2010	392	391	E	"Diaphram wall wharf pile foundation structure,Connexion souterraine 续墙头桩桀 structure"	FIRST ENGINEERING COMPANY OF CCCC FOURTH HARBOR ENGINEERING CO LTD	0	0	0	0	392	0	0	0	0	0	0	0	392	0
2	CN-1895777-A	CN	2005	297	296	B	"Une sorte de molécule messagère emballée,Porous molecular-sieve catalyst for assembling carbide and its preparation"	UNIV BEIJING CHEMICAL	0	7	290	0	0	0	0	0	0	0	0	0	297	0
3	CN-1634601-A	CN	2003	680	288	A	"Pour un type de méthode bactériostatique d'instrument de traitement médical,Method for sterilizing medical appliance"	JILIN PROVINCE ZHONGLI IND CO	631	8	0	0	0	4	6	31	677	0	0	0	3	0
4	CN-1262969-A	CN	2000	290	288	B	"Catalyst using TiO2 as carrier to load metal nitride Mo2N,TiO"	UNIV NANKAI	0	7	283	0	0	0	0	0	0	0	0	0	290	0
5	CN-1470327-A	CN	2002	286	286	C	"Metal nitride catalyst preparing method and catalyst,Une sorte de métalliseur"	CHINA PETROCHEMICAL CORP	0	7	279	0	0	0	0	0	0	0	0	0	286	0

1	KR-20000051826-A	KR	1999	192	179	C	"New organomattalic complex molecule for the fabrication of organic light emitting diodes,신규한 착물 및 그의 제조 방법과 이를 이용한 유기 발광 소자"	LG CHEMICAL LTD	0	0	162	0	0	0	0	30	12	0	0	168	0	12
2	KR-20110003229-A	KR	2009	379	175	A	"하이브리드 수술용 로봇 시스템 및 수술용 로봇 제어방법,Hybrid surgical robot system and control method thereof"	ETERNE INC	360	6	0	0	0	1	7	5	366	0	1	4	1	7
3	KR-20000074034-A	KR	1999	202	145	H	"Ultra-slim Repeater with Variable Attenuator,케이블 손실 보상이 가능한 초소형 중계기"	ACE TECH	0	0	0	0	0	0	10	192	197	0	0	4	0	1
4	KR-20120009843-A	KR	2010	168	142	G	"이동단말기 및 그의 어플리케이션 공유 방법,Mobile terminal and method for sharing applications thereof"	LG ELECTRONICS INC	0	0	0	0	0	0	17	151	155	0	0	3	2	8
5	KR-20190000980-A	KR	2017	148	141	H	"A personal Healthcare System,퍼스널 헬스케어 시스템"	LEE DONG WON	15	38	0	1	15	24	19	36	1	0	0	147	0	0

1	WO-2004114391-A1	WO	2004	2859	1667	H	"Dispositif a semi-conducteur et son procede de production, et dispositif electronique,Semiconductor device, its manufacturing method, and electronic device,Dispositifs à semi-conducteurs et leurs méthodes de fabrication et dispositifs électroniques"	"KAWASAKI MASASHI,SHARP KK,SUGIHARA TOSHINORI,OHNO HIDEO"	1	3	10	1	0	2	666	2176	2843	7	4	1	1	3
2	WO-9201047-A1	WO	1991	2421	1413	C	"Methods for producing members of specific binding pairs,Procede de production de chainon de paires a liaison specifique"	"CAMBRIDGE ANTIBODY TECH,MEDICAL RES COUNCIL"	797	2	1410	0	0	0	212	0	850	2	780	1	4	784
3	WO-9633735-A1	WO	1996	1764	1023	C	"Anticorps humains derives d'une xenosouris immunisee,Human antibodies derived from immunized xenomice"	CELL GENESYS INC	645	2	1003	0	0	0	114	0	616	29	510	2	1	606
4	WO-9634096-A1	WO	1995	1712	1022	C	"Anticorps humains derives de xeno-souris immunisees,Human antibodies derived from immunized xenomice"	CELL GENESYS INC	626	2	977	0	0	0	107	0	553	17	523	2	0	617
5	WO-9307278-A1	WO	1992	1137	995	A	"Sequence d'adn synthetique ayant une action insecticide accrue dans le mais,Synthetic dna sequence having enhanced insecticidal activity in maize"	CIBA GEIGY AG	515	1	616	0	0	0	5	0	81	0	230	0	1	825

4. Citation transnationale

Par exemple, un modèle dans lequel les gazettes américaines sont utilisées dans les examens JP. Cette fois, nous avons ciblé les demandes de six offices de brevets, dont JP, US, CN, EP et KR, qui seraient les cinq principaux offices de brevets, ainsi que les demandes de brevet internationales (WO). Est extrait. Habituellement, la littérature écrite dans la langue du pays est utilisée comme référence, mais s'il n'y a pas de littérature ayant une technologie similaire, la littérature étrangère peut être introduite. En regardant le pays, la plupart d'entre eux sont américains, mais il semble qu'il existe de nombreux systèmes de télécommunications (G, H). Impression qu'il existe de nombreuses entreprises japonaises. Est-il facile à utiliser comme document technique?

<détails>

code </ summary>

SELECT 
  pubnum, 
  ipcs,
  total_cit_count,unique_cit_count,
  applicants,
  titles,
  US,JP,EP,CN,KR,WO
  FROM `bqml_tutorial.fcitdata`
  WHERE US>0 AND JP>0 AND EP >0 AND CN>0 AND KR >0 AND WO >0
  #GROUP BY ipcs,pubnum,total_cit_count,unique_cit_count,titles,applicants
  ORDER BY total_cit_count DESC
  LIMIT 10

<détails>

Liste </ summary>

pubnum	ipcs	total_cit_count	unique_cit_count	applicants	titles	US	JP	EP	CN	KR	WO
US-5892900-A	G	4035	1944	INTERTRUST TECH CORP	Systems and methods for secure transaction management and electronic rights protection	3955	2	19	29	4	26
US-2003189401-A1	C	3849	1958	INT MFG & ENG SERVICES CO LTD	Organic electroluminescent device	3820	1	16	1	3	8
US-2007194379-A1	G	3592	1833	JAPAN SCIENCE & TECH AGENCY	Amorphous Oxide And Thin Film Transistor	3574	1	7	1	2	7
US-2007108446-A1	H	3501	1758	SEMICONDUCTOR ENERGY LAB	Semiconductor device and manufacturing method thereof	3464	4	11	7	13	2
US-2006244107-A1	H	3493	1844	SUGIHARA TOSHINORI,KAWASAKI MASASHI,OHNO HIDEO	Semiconductor device, manufacturing method, and electronic device	3461	2	12	7	3	8
US-2006108636-A1	H	3475	1777	TOKYO INST TECH	Amorphous oxide and field effect transistor	3452	6	10	3	1	3
US-2006110867-A1	H	3454	1752	TOKYO INST TECH	Field effect transistor manufacturing method	3426	4	9	5	4	6
US-2006113565-A1	H	3439	1763	TOKYO INST TECH	Electric elements and circuits utilizing amorphous oxides	3412	4	3	10	2	8
US-2008038882-A1	H	3401	1734	TAKECHI KAZUSHIGE,NAKATA MITSURU	Thin-film device and method of fabricating the same	3342	13	8	24	4	10
US-2007090365-A1	H	3398	1742	CANON KK	Field-effect transistor including transparent oxide and light-shielding member, and display utilizing the transistor	3384	3	1	8	1	1

Il peut être plus facile de comprendre cette zone en la visualisant sous forme de diagramme de réseau.

5. Correction par le temps

On peut dire que les documents cités de la nouvelle ère sont désavantageux car le temps ne s'est pas écoulé depuis leur publication, et les documents de l'ancienne ère ont tendance à être cités plus fréquemment, alors je l'ai corrigé un peu. Si vous travaillez sérieusement dessus, il y a biais de déconnexion, mais ce processus N'est-ce pas cette fois. Les applications américaines et 2008-2009 sont arrivées au sommet.

<détails>

code </ summary>

DECLARE kotoshi INT64;
SET kotoshi = CAST(FORMAT_DATE("%Y",CURRENT_DATE()) AS INT64);

SELECT pubnum,appcountry,appyear,total_cit_count,unique_cit_count,kotoshi - CAST(appyear AS INT64) AS pub_interval, total_cit_count / (kotoshi - CAST(appyear AS INT64)) AS ave_cit,ipcs,	titles
FROM `bqml_tutorial.fcitdata`
ORDER BY ave_cit DESC
LIMIT 20

<détails>

Liste </ summary>

pubnum	appcountry	appyear	total_cit_count	unique_cit_count	pub_interval	ave_cit	ipcs	titles
US-2009278122-A1	US	2009	3164	1725	11	287.6	H	Amorphous oxide and thin film transistor
US-2009280600-A1	US	2009	3163	1727	11	287.5	G	Amorphous oxide and thin film transistor
US-2010092800-A1	US	2009	3150	1678	11	286.4	B	Substrate for growing wurtzite type crystal and method for manufacturing the same and semiconductor device
US-2010065844-A1	US	2009	3149	1677	11	286.3	H	Thin film transistor and method of manufacturing thin film transistor
US-7732819-B2	US	2008	3341	1737	12	278.4	H	Semiconductor device and manufacturing method thereof
US-2009134399-A1	US	2008	3303	1708	12	275.3	C	Semiconductor Device and Method for Manufacturing the Same
US-2009073325-A1	US	2008	3298	1705	12	274.8	G	"Semiconductor device and method for manufacturing the same, and electric device"
US-2008224133-A1	US	2008	3285	1714	12	273.8	H	Thin film transistor and organic light-emitting display device having the thin film transistor
US-2008258141-A1	US	2008	3279	1720	12	273.3	H	"Thin film transistor, method of manufacturing the same, and flat panel display having the same"
US-2009068773-A1	US	2008	3275	1709	12	272.9	H	Method for fabricating pixel structure of active matrix organic light-emitting diode

6. Référence: documents non-brevet avec de nombreuses citations

Les documents non brevetés étant inclus dans les documents cités, j'ai essayé de les extraire (pas d'identification de nom). Par rapport aux documents de brevet, c'est beaucoup plus compliqué à mettre en place ... Le numéro un cité est l'article du séquenceur de la séquence de gènes suivante à l'exception des séquences étranges.

ALTSCHUL ET AL., J. MOL. BIOL., vol. 215, 1990, pages 403 - 410

<détails>

code </ summary>

SELECT 
  cit.npl_text AS title,
  COUNT(cit.npl_text) AS count,
  STRING_AGG(DISTINCT(SUBSTR(main.publication_number,0,2))) AS cit_country,
  SUM(CASE SUBSTR(ipcs.code,0,1) WHEN 'A' THEN 1 ELSE 0 END) as IPC_A,
  SUM(CASE SUBSTR(ipcs.code,0,1) WHEN 'B' THEN 1 ELSE 0 END) as IPC_B,
  SUM(CASE SUBSTR(ipcs.code,0,1) WHEN 'C' THEN 1 ELSE 0 END) as IPC_C,
  SUM(CASE SUBSTR(ipcs.code,0,1) WHEN 'D' THEN 1 ELSE 0 END) as IPC_D,
  SUM(CASE SUBSTR(ipcs.code,0,1) WHEN 'E' THEN 1 ELSE 0 END) as IPC_E,
  SUM(CASE SUBSTR(ipcs.code,0,1) WHEN 'F' THEN 1 ELSE 0 END) as IPC_F,
  SUM(CASE SUBSTR(ipcs.code,0,1) WHEN 'G' THEN 1 ELSE 0 END) as IPC_G,
  SUM(CASE SUBSTR(ipcs.code,0,1) WHEN 'H' THEN 1 ELSE 0 END) as IPC_H,
  SUM(CASE SUBSTR(main.publication_number,0,2) WHEN 'US' THEN 1 ELSE 0 END) as US,
  SUM(CASE SUBSTR(main.publication_number,0,2) WHEN 'JP' THEN 1 ELSE 0 END) as JP,
  SUM(CASE SUBSTR(main.publication_number,0,2) WHEN 'EP' THEN 1 ELSE 0 END) as EP,
  SUM(CASE SUBSTR(main.publication_number,0,2) WHEN 'KR' THEN 1 ELSE 0 END) as KR,
  SUM(CASE SUBSTR(main.publication_number,0,2) WHEN 'CN' THEN 1 ELSE 0 END) as CN,
  SUM(CASE SUBSTR(main.publication_number,0,2) WHEN 'WO' THEN 1 ELSE 0 END) as WO
FROM 
  `patents-public-data.patents.publications_201912` as main,
  UNNEST(main.citation) AS cit,
  UNNEST(main.ipc) AS ipcs
WHERE cit.npl_text NOT IN ('','None','NEANT','NICHTS ERMITTELT','NICHTS-ERMITTELT','No Search','International Search Report.','No further relevant documents disclosed')
  AND ipcs.first = TRUE
GROUP BY cit.npl_text
ORDER BY count DESC

<détails>

Liste </ summary>

title	count	cit_country	IPC_A	IPC_B	IPC_C	IPC_D	IPC_E	IPC_F	IPC_G	IPC_H	US	EP	WO
"PATENT ABSTRACTS OF JAPAN vol. 2003, no. 12 5 December 2003 (2003-12-05)"	3694	"FR,NL,EP,LU,CH,GR,WO"	317	805	407	60	59	461	819	766	0	1922	1354
PATENT ABSTRACTS OF JAPAN	3117	"WO,FR,US,DE,NL,EP"	367	659	505	17	73	260	703	533	1	1258	1717
"ALTSCHUL ET AL., J. MOL. BIOL., vol. 215, 1990, pages 403 - 410"	2385	"WO,EP"	516	2	1756	1	0	0	109	1	0	964	1421
"Kraft et al., ""Linkage disequilibrium and fingerprinting in sugar beet,"" Theor Appl Genet, 101:323-326, 2000."	2256	US	2193	0	63	0	0	0	0	0	2256	0	0
"SAMBROOK ET AL.: ""Molecular Cloning: A Laboratory Manual"", 1989, COLD SPRING HARBOR LABORATORY PRESS"	2086	"WO,EP"	528	14	1389	0	0	1	153	1	0	987	1099
"JONES ET AL., NATURE, vol. 321, 1986, pages 522 - 525"	1969	"EP,WO"	584	6	1221	0	0	0	158	0	0	881	1088
"WARD ET AL., NATURE, vol. 341, 1989, pages 544 - 546"	1908	"EP,WO"	504	3	1233	0	0	0	168	0	0	816	1092
"BIRD ET AL., SCIENCE, vol. 242, 1988, pages 423 - 426"	1900	"EP,WO"	555	4	1199	0	0	0	142	0	0	739	1161
"CLACKSON ET AL., NATURE, vol. 352, 1991, pages 624 - 628"	1784	"WO,EP"	536	9	1035	0	0	0	204	0	0	728	1056
"Meghji et al., ""Inbreeding depression, inbred and hybrid grain yields, and other traits of maize genotypes representing three eras,"" Crop Science, 24:545-549, 1984."	1754	US	1698	0	56	0	0	0	0	0	1754	0	0

7. À l'exclusion des citations des candidats américains

Puisqu'il y a de nombreuses citations de candidats dans les demandes américaines, la clause WHERE a été corrigée dans l'intention de les exclure.

~~Seulement des pièces différentes
WHERE SUBSTR(main.publication_number,0,2) IN ('US','JP','EP','CN','KR','WO') AND pubnum IS NOT NULL AND ipcs.first = TRUE AND cit.category IN ('EXA','SEA','ISR')

Ensuite, dans le classement suivant.