Researchers' first name variations, based on ORCID

First name variation based on ORCID, as present in Dimensions' open data platform.Query usedWITH name_variant AS ( SELECT TRIM(LOWER(pname.given_names)) AS first_name, TRIM(LOWER(variant.content)) AS variant_name, COUNT(DISTINCT s.orcid_identifier.path) AS researcher_count FROM ds-open-datasets.orcid.summaries_2024 AS s, UNNEST(s.person.other_names.names) AS variant JOIN UNNEST([s.person.name]) AS pname WHERE pname.given_names IS NOT NULL AND variant.content IS NOT NULL AND TRIM(LOWER(pname.given_names)) != TRIM(LOWER(variant.content)) AND NOT REGEXP_CONTAINS(variant.content, r"\s") -- remove variants with whitespace AND NOT REGEXP_CONTAINS(variant.content, r"[\?\.,]") -- remove variants with punctuation AND NOT REGEXP_CONTAINS(LOWER(variant.content), r"^(dr|professor|phd|doctor|n/a|reviewer|sociologist|lecturer|lecture|researcher|architect|everyone|physician)\b") AND NOT REGEXP_CONTAINS(LOWER(variant.content), r"\b(dr|professor|mr|ms|mrs|phd|doctor|n/a|reviewer|sociologist|lecturer|lecture|researcher|architect|everyone|physician)\b") GROUP BY first_name, variant_name ORDER BY researcher_count DESC)SELECT *FROM name_varianWITH name_variant AS ( SELECT TRIM(LOWER(pname.given_names)) AS first_name, TRIM(LOWER(variant.content)) AS variant_name, COUNT(DISTINCT s.orcid_identifier.path) AS researcher_count FROM ds-open-datasets.orcid.summaries_2024 AS s, UNNEST(s.person.other_names.names) AS variant JOIN UNNEST([s.person.name]) AS pname WHERE pname.given_names IS NOT NULL AND variant.content IS NOT NULL AND TRIM(LOWER(pname.given_names)) != TRIM(LOWER(variant.content)) AND NOT REGEXP_CONTAINS(variant.content, r"\s") -- remove variants with whitespace AND NOT REGEXP_CONTAINS(variant.content, r"[\?\.,]") -- remove variants with punctuation AND NOT REGEXP_CONTAINS(LOWER(variant.content), r"^(dr|professor|phd|doctor|n/a|reviewer|sociologist|lecturer|lecture|researcher|architect|everyone|physician)\b") AND NOT REGEXP_CONTAINS(LOWER(variant.content), r"\b(dr|professor|mr|ms|mrs|phd|doctor|n/a|reviewer|sociologist|lecturer|lecture|researcher|architect|everyone|physician)\b") GROUP BY first_name, variant_name ORDER BY researcher_count DESC)SELECT *FROM name_variantWHERE researcher_count > 1;WHERE researcher_count > 1;本数据集基于Dimensions开放数据平台中的开放研究者与贡献者标识符（ORCID）数据，用于提取研究者的名变体信息。所用查询语句如下： WITH name_variant AS ( SELECT TRIM(LOWER(pname.given_names)) AS first_name, TRIM(LOWER(variant.content)) AS variant_name, COUNT(DISTINCT s.orcid_identifier.path) AS researcher_count FROM ds-open-datasets.orcid.summaries_2024 AS s, UNNEST(s.person.other_names.names) AS variant JOIN UNNEST([s.person.name]) AS pname WHERE pname.given_names IS NOT NULL AND variant.content IS NOT NULL AND TRIM(LOWER(pname.given_names)) != TRIM(LOWER(variant.content)) AND NOT REGEXP_CONTAINS(variant.content, r"s") -- 移除含空白字符的变体 AND NOT REGEXP_CONTAINS(variant.content, r"[?.,]") -- 移除含标点符号的变体 AND NOT REGEXP_CONTAINS(LOWER(variant.content), r"^(dr|professor|phd|doctor|n/a|reviewer|sociologist|lecturer|lecture|researcher|architect|everyone|physician)") -- 移除以指定头衔或身份词开头的变体 AND NOT REGEXP_CONTAINS(LOWER(variant.content), r"(dr|professor|mr|ms|mrs|phd|doctor|n/a|reviewer|sociologist|lecturer|lecture|researcher|architect|everyone|physician)") -- 移除包含指定头衔或身份词的变体 GROUP BY first_name, variant_name ORDER BY researcher_count DESC)SELECT *FROM name_varianWITH name_variant AS ( SELECT TRIM(LOWER(pname.given_names)) AS first_name, TRIM(LOWER(variant.content)) AS variant_name, COUNT(DISTINCT s.orcid_identifier.path) AS researcher_count FROM ds-open-datasets.orcid.summaries_2024 AS s, UNNEST(s.person.other_names.names) AS variant JOIN UNNEST([s.person.name]) AS pname WHERE pname.given_names IS NOT NULL AND variant.content IS NOT NULL AND TRIM(LOWER(pname.given_names)) != TRIM(LOWER(variant.content)) AND NOT REGEXP_CONTAINS(variant.content, r"s") -- 移除含空白字符的变体 AND NOT REGEXP_CONTAINS(variant.content, r"[?.,]") -- 移除含标点符号的变体 AND NOT REGEXP_CONTAINS(LOWER(variant.content), r"^(dr|professor|phd|doctor|n/a|reviewer|sociologist|lecturer|lecture|researcher|architect|everyone|physician)") -- 移除以指定头衔或身份词开头的变体 AND NOT REGEXP_CONTAINS(LOWER(variant.content), r"(dr|professor|mr|ms|mrs|phd|doctor|n/a|reviewer|sociologist|lecturer|lecture|researcher|architect|everyone|physician)") -- 移除包含指定头衔或身份词的变体 GROUP BY first_name, variant_name ORDER BY researcher_count DESC)SELECT *FROM name_variantWHERE researcher_count > 1;WHERE researcher_count > 1;