Help: SQL for PATSTAT

[Veronika]

Hallo zusammen,

ich benötige für eine wissenschaftliche Arbeit einen Datensatz über AI Patente in den letzten 8 Jahren, dann welche Unternehmen dies waren in Europa. Dann soll im Output außerdem die Anzahl an Forward citations dieser AI patente in den letzten 5 Jahren enthalten sein und auch noch die generelle Anzahl an patenten der letzten 3 Jahre. Alles ausgegeben in der gleichen Tabelle. Leider komme ich nicht weiter bzw. kommen verschiedenste Fehlermeldungen, wenn die den Code für Forward Citations und die generellen Patente der letzten 3 Jahre anhänge. Deshalb habe ich bisher nur Folgendes:
SELECT TOP 3
p.psn_name AS inventor_name,
a.appln_filing_year,
LEFT(ipc.ipc_class_symbol, 4) AS tech_field,
COUNT(DISTINCT a.appln_id) AS patent_count
FROM tls201_appln AS a
INNER JOIN tls207_pers_appln AS pa
ON a.appln_id = pa.appln_id
INNER JOIN tls206_person AS p
ON pa.person_id = p.person_id
INNER JOIN tls209_appln_ipc AS ipc
ON a.appln_id = ipc.appln_id
WHERE a.appln_filing_year >= YEAR(GETDATE()) - 8
AND p.person_ctry_code IN ('AT', 'BE', 'DE', 'FR', 'GB', 'IT', 'NL', 'ES', 'SE', 'CH')
AND (ipc.ipc_class_symbol LIKE 'G06N%'
OR ipc.ipc_class_symbol LIKE 'G05B%'
OR ipc.ipc_class_symbol LIKE 'G06F%')
AND ipc.ipc_position = 'L'
AND pa.applt_seq_nr > 0
GROUP BY
p.psn_name,
a.appln_filing_year,
LEFT(ipc.ipc_class_symbol, 4)
ORDER BY
patent_count DESC,
p.psn_name;


Könnte jemand mir helfen, wie ich möglichst schlau die anderen zwei Kriterien dranhänge?
Vielen Dank im Voraus!

LG

 

[Alexy44]

Du machst es dir vor allem schwer, weil du alles in eine Abfrage packen willst. Am einfachsten ist es, das zu trennen: eine Basisabfrage für die KI-Patente (8 Jahre) und dann zwei LEFT JOIN-Subqueries – eine für die Forward Citations (5 Jahre) über tls212_citation und eine für die Gesamtzahl der Patente (3 Jahre) je Unternehmen. Alles in einem GROUP BY führt fast immer zu Problemen.

 

[ukulele]

Schon das TOP 3 in dem Kontext macht keinen Sinn. Das bezieht sich vermutlich auf die Anzahl der Patente der letzten 3 Jahre, aber inhaltlich würde es ja dann die anderen Werte beeinträchtigen. Dazu kommt, das die Sortierung bei TOP relevant wird - die bezieht sich aber auf die Anzahl der Patente?

Frage wäre also zunächst: Liefert dein jetziger Code, ohne die Einschränkung TOP 3, die korrekten Daten für die Anzahl der AI Patente pro inventor_name der letzten 8 Jahre?

Dann irritiert mich das COUNT(DISTINCT a.appln_id). Kann die appln_id mehrfach vorkommen? Das sollte meiner Meinung nach nicht der Fall sein, ich kenne aber die Daten nicht. Die Joins sehen so aus als müsste die ID von sich aus einmalig sein. Wenn das so ist, dann könnte man z.B. schonmal die nicht AI Patente und die AI Patente zählen, etwa so:

SELECT
p.psn_name AS inventor_name,
a.appln_filing_year,
LEFT(ipc.ipc_class_symbol, 4) AS tech_field,
    sum(    CASE
            WHEN    ipc.ipc_class_symbol LIKE 'G06N%'
            OR        ipc.ipc_class_symbol LIKE 'G05B%'
            OR        pc.ipc_class_symbol LIKE 'G06F%'
            THEN    1
            ELSE    0
            END ) AS AI_patent_count_8y,
    sum(    CASE
            WHEN    a.appln_filing_year >= YEAR(GETDATE()) - 3
            THEN    1
            ELSE    0
            END ) AS patent_count_3y --inklusive AI_patent_count
FROM tls201_appln AS a
INNER JOIN tls207_pers_appln AS pa
ON a.appln_id = pa.appln_id
INNER JOIN tls206_person AS p
ON pa.person_id = p.person_id
INNER JOIN tls209_appln_ipc AS ipc
ON a.appln_id = ipc.appln_id
WHERE a.appln_filing_year >= YEAR(GETDATE()) - 8
AND p.person_ctry_code IN ('AT', 'BE', 'DE', 'FR', 'GB', 'IT', 'NL', 'ES', 'SE', 'CH')
AND ipc.ipc_position = 'L'
AND pa.applt_seq_nr > 0
GROUP BY
p.psn_name,
a.appln_filing_year,
LEFT(ipc.ipc_class_symbol, 4)
ORDER BY
patent_count DESC,
p.psn_name;

Ich tausche also count() gegen sum(CASE ...) und betrachte die Eigenschaften jedes Datensatzes um zu entscheiden, ob er für den Wert gezählt werden darf oder nicht.

Die Ermittlung für Forward Citations kann ich aus dem Code nicht ableiten. Eventuell würde dieser a.appln_id mehrfach auflisten, das darf in dem Konstrukt aber nicht passieren.