[continues from last 2 posts]
Eventually we see
whether is there some bias due to application kind
Anyway even
in this case we notice that the weight of invention patents kind applications
is so high that % of patents with an valid inventor are always near to the one
of that kind of application (that may change from country to country, FI is T
in Austria, A in Belgium and so on…)
Exceptions
are only those countries (as BG for instance) where the number of PCT patents
(W) is near to the figure of patents of invention, and in such cases is an
average of the two.
APPLN
|
'N'
|
'V'
|
'Y'
|
Total
|
||||
KIND
|
#
|
%
|
#
|
%
|
#
|
%
|
||
'AT'
|
'A'
|
805.664
|
4.02%
|
12852
|
64.18%
|
6367.2771
|
31.80%
|
20024.9411
|
'AT'
|
'D'
|
2
|
100.00%
|
2
|
||||
'AT'
|
'D2'
|
764
|
100.00%
|
764
|
||||
'AT'
|
'E'
|
1
|
100.00%
|
1
|
||||
'AT'
|
'F'
|
202
|
100.00%
|
202
|
||||
'AT'
|
'K'
|
1
|
100.00%
|
1
|
||||
'AT'
|
'T'
|
3140.0253
|
1.37%
|
558
|
0.24%
|
225662.1863
|
98.39%
|
229360.2116
|
'AT'
|
'U'
|
4.5
|
0.05%
|
5274
|
63.79%
|
2989.4801
|
36.16%
|
8267.9801
|
'AT'
|
'W'
|
41.7493
|
0.85%
|
22
|
0.45%
|
4861.7121
|
98.71%
|
4925.4614
|
'BE'
|
'A'
|
5391.4134
|
1.92%
|
273099
|
97.27%
|
2273.1853
|
0.81%
|
280763.5987
|
'BE'
|
'D'
|
2
|
100.00%
|
2
|
||||
'BE'
|
'D2'
|
2065
|
100.00%
|
2065
|
||||
'BE'
|
'F'
|
4
|
100.00%
|
4
|
||||
'BE'
|
'W'
|
9.4944
|
0.58%
|
105
|
6.45%
|
1512.4952
|
92.96%
|
1626.9896
|
'BG'
|
'A'
|
0.5
|
0.02%
|
459
|
14.60%
|
2684.7454
|
85.39%
|
3144.2454
|
'BG'
|
'D'
|
1.1666
|
0.04%
|
1
|
0.03%
|
3192.5028
|
99.93%
|
3194.6694
|
'BG'
|
'D2'
|
105
|
100.00%
|
105
|
||||
'BG'
|
'F'
|
4
|
100.00%
|
4
|
||||
'BG'
|
'U'
|
1
|
0.11%
|
26
|
2.97%
|
847.7436
|
96.91%
|
874.7436
|
'BG'
|
'W'
|
3.4999
|
1.32%
|
7
|
2.64%
|
254.4978
|
96.04%
|
264.9977
|
'BG'
|
'CY'
|
21.6039
|
4.41%
|
36
|
7.35%
|
432.3883
|
88.24%
|
489.9922
|
'BG'
|
'A'
|
21.6039
|
4.74%
|
32
|
7.02%
|
402.3884
|
88.24%
|
455.9923
|
'BG'
|
'D2'
|
4
|
100.00%
|
4
|
||||
'BG'
|
'W'
|
29.9999
|
100.00%
|
29.9999
|
||||
'CZ'
|
'A'
|
3.1456
|
0.02%
|
76
|
0.46%
|
16296.026
|
99.52%
|
16375.1716
|
'CZ'
|
'D2'
|
551
|
100.00%
|
551
|
||||
'CZ'
|
'F'
|
5
|
100.00%
|
5
|
||||
'CZ'
|
'U'
|
2.6666
|
0.02%
|
74
|
0.59%
|
12550.9257
|
99.39%
|
12627.5923
|
'CZ'
|
'W'
|
23.4498
|
2.12%
|
5
|
0.45%
|
1077.5475
|
97.43%
|
1105.9973
|
'DE'
|
'82'
|
1
|
100.00%
|
1
|
||||
'DE'
|
'A'
|
6024.2362
|
0.57%
|
380921
|
35.78%
|
677665.9428
|
63.65%
|
1064611.179
|
'DE'
|
'B'
|
147.9987
|
19.63%
|
605
|
80.24%
|
1
|
0.13%
|
753.9987
|
'DE'
|
'C'
|
4.4
|
0.06%
|
7819
|
99.86%
|
6.6
|
0.08%
|
7830
|
'DE'
|
'D'
|
550.4997
|
0.54%
|
101211
|
99.45%
|
12.9995
|
0.01%
|
101774.4992
|
'DE'
|
'D2'
|
107037
|
100.00%
|
107037
|
||||
'DE'
|
'E'
|
25
|
100.00%
|
25
|
||||
'DE'
|
'F'
|
1826
|
100.00%
|
1826
|
||||
'DE'
|
'G'
|
1
|
100.00%
|
1
|
||||
'DE'
|
'T'
|
315.1345
|
0.12%
|
723
|
0.28%
|
256510.4877
|
99.60%
|
257548.6222
|
'DE'
|
'U'
|
5
|
0.00%
|
491701
|
99.99%
|
52
|
0.01%
|
491758
|
'DE'
|
'W'
|
695.3552
|
2.11%
|
212
|
0.64%
|
32084.3104
|
97.25%
|
32991.6656
|
'DK'
|
'A'
|
7870.2426
|
12.15%
|
40955
|
63.24%
|
15939.4568
|
24.61%
|
64764.6994
|
'DK'
|
'C'
|
2
|
100.00%
|
2
|
||||
'DK'
|
'D'
|
6
|
100.00%
|
6
|
||||
'DK'
|
'D2'
|
5356
|
100.00%
|
5356
|
||||
'DK'
|
'F'
|
324
|
100.00%
|
324
|
||||
'DK'
|
'T'
|
4.8016
|
0.01%
|
242
|
0.65%
|
37230.8817
|
99.34%
|
37477.6833
|
'DK'
|
'U'
|
7.0002
|
0.26%
|
71
|
2.59%
|
2664.9912
|
97.16%
|
2742.9914
|
'DK'
|
'W'
|
421.0603
|
4.99%
|
74
|
0.88%
|
7945.7786
|
94.13%
|
8440.8389
|
'EE'
|
'A'
|
50.4771
|
2.40%
|
56
|
2.66%
|
2000.5263
|
94.95%
|
2107.0034
|
'EE'
|
'D'
|
1
|
100.00%
|
1
|
||||
'EE'
|
'D2'
|
8
|
100.00%
|
8
|
||||
'EE'
|
'F'
|
3
|
100.00%
|
3
|
||||
'EE'
|
'U'
|
4.65
|
0.57%
|
44
|
5.37%
|
771.3494
|
94.07%
|
819.9994
|
'EE'
|
'W'
|
2.4165
|
1.73%
|
2
|
1.43%
|
135.0838
|
96.83%
|
139.5003
|
'ES'
|
'A'
|
3042.9784
|
12.27%
|
2355
|
9.49%
|
19405.9269
|
78.24%
|
24803.9053
|
'ES'
|
'D'
|
5
|
83.33%
|
1
|
16.67%
|
6
|
||
'ES'
|
'D2'
|
5648
|
100.00%
|
5648
|
||||
'ES'
|
'F'
|
124
|
100.00%
|
124
|
||||
'ES'
|
'P'
|
40.714
|
84.82%
|
4
|
8.33%
|
3.2858
|
6.85%
|
47.9998
|
'ES'
|
'T'
|
5329.4151
|
4.70%
|
296
|
0.26%
|
107809.105
|
95.04%
|
113434.5201
|
'ES'
|
'U'
|
835.8427
|
2.80%
|
139
|
0.47%
|
28861.9612
|
96.73%
|
29836.8039
|
'ES'
|
'W'
|
114.2446
|
1.31%
|
71
|
0.81%
|
8565.1699
|
97.88%
|
8750.4145
|
'FI'
|
'A'
|
132.8338
|
0.20%
|
22615
|
33.81%
|
44147.8138
|
66.00%
|
66895.6476
|
'FI'
|
'B'
|
13.9998
|
87.50%
|
2
|
12.50%
|
15.9998
|
||
'FI'
|
'C'
|
2
|
100.00%
|
2
|
||||
'FI'
|
'D'
|
4
|
100.00%
|
4
|
||||
'FI'
|
'D2'
|
2161
|
100.00%
|
2161
|
||||
'FI'
|
'F'
|
231
|
100.00%
|
231
|
||||
'FI'
|
'U'
|
88
|
1.55%
|
5602.9769
|
98.45%
|
5690.9769
|
||
'FI'
|
'W'
|
121.2539
|
1.06%
|
54
|
0.47%
|
11220.6756
|
98.46%
|
11395.9295
|
'FR'
|
'A'
|
125087.6694
|
57.00%
|
58954
|
26.86%
|
35407.5073
|
16.13%
|
219449.1767
|
'FR'
|
'D'
|
3
|
15.00%
|
13
|
65.00%
|
3.9998
|
20.00%
|
19.9998
|
'FR'
|
'D2'
|
9810
|
100.00%
|
9810
|
||||
'FR'
|
'E'
|
2917.9899
|
9.72%
|
23968
|
79.85%
|
3130.9945
|
10.43%
|
30016.9844
|
'FR'
|
'F'
|
814
|
99.88%
|
1
|
0.12%
|
815
|
||
'FR'
|
'M'
|
3726
|
83.81%
|
719.9913
|
16.19%
|
4445.9913
|
||
'FR'
|
'W'
|
751.5171
|
1.81%
|
222
|
0.54%
|
40459.8108
|
97.65%
|
41433.3279
|
'GB'
|
'4'
|
1
|
100.00%
|
1
|
||||
'GB'
|
'A'
|
1516.2473
|
0.46%
|
210828
|
63.62%
|
119063.5484
|
35.93%
|
331407.7957
|
'GB'
|
'D'
|
36
|
94.74%
|
1.9999
|
5.26%
|
37.9999
|
||
'GB'
|
'D2'
|
22979
|
100.00%
|
22979
|
||||
'GB'
|
'F'
|
1704
|
100.00%
|
1704
|
||||
'GB'
|
'T'
|
1
|
100.00%
|
1
|
||||
'GB'
|
'W'
|
1355.3921
|
2.41%
|
329
|
0.59%
|
54480.5532
|
97.00%
|
56164.9453
|
'GR'
|
'A'
|
4041.8495
|
84.68%
|
322
|
6.75%
|
408.9987
|
8.57%
|
4772.8482
|
'GR'
|
'D2'
|
172
|
100.00%
|
172
|
||||
'GR'
|
'F'
|
1
|
100.00%
|
1
|
||||
'GR'
|
'T'
|
42.9768
|
0.88%
|
82
|
1.69%
|
4734.8227
|
97.43%
|
4859.7995
|
'GR'
|
'U'
|
133.9999
|
96.40%
|
5
|
3.60%
|
138.9999
|
||
'GR'
|
'W'
|
8.8
|
1.52%
|
5
|
0.86%
|
565.1979
|
97.62%
|
578.9979
|
'HU'
|
'A'
|
9.2832
|
0.05%
|
9301
|
48.29%
|
9952.0267
|
51.67%
|
19262.3099
|
'HU'
|
'D'
|
2
|
100.00%
|
2
|
||||
'HU'
|
'D2'
|
1553
|
100.00%
|
1553
|
||||
'HU'
|
'F'
|
7
|
100.00%
|
7
|
||||
'HU'
|
'P'
|
15
|
100.00%
|
15
|
||||
'HU'
|
'U'
|
1.5833
|
0.05%
|
1146
|
37.35%
|
1920.6547
|
62.60%
|
3068.238
|
'HU'
|
'W'
|
10.2289
|
0.72%
|
10
|
0.71%
|
1392.464
|
98.57%
|
1412.6929
|
'IE'
|
'A'
|
53.3755
|
0.81%
|
1983
|
30.27%
|
4513.5917
|
68.91%
|
6549.9672
|
'IE'
|
'D2'
|
230
|
100.00%
|
230
|
||||
'IE'
|
'F'
|
18
|
100.00%
|
18
|
||||
'IE'
|
'W'
|
17.8749
|
1.19%
|
14
|
0.93%
|
1473.1152
|
97.88%
|
1504.9901
|
'IE'
|
'IT'
|
109618.3051
|
64.40%
|
45064
|
26.47%
|
15533.4011
|
9.13%
|
170215.7062
|
'IE'
|
'A'
|
90624.8414
|
88.97%
|
5171
|
5.08%
|
6058.6198
|
5.95%
|
101854.4612
|
'IE'
|
'D'
|
1.9999
|
20.00%
|
6
|
60.00%
|
2
|
20.00%
|
9.9999
|
'IE'
|
'D2'
|
33486
|
100.00%
|
33486
|
||||
'IE'
|
'F'
|
427
|
100.00%
|
427
|
||||
'IE'
|
'T'
|
2
|
66.67%
|
1
|
33.33%
|
3
|
||
'IE'
|
'U'
|
18891.831
|
70.86%
|
5820
|
21.83%
|
1948.4676
|
7.31%
|
26660.2986
|
'IE'
|
'W'
|
97.6328
|
1.26%
|
152
|
1.96%
|
7524.3137
|
96.79%
|
7773.9465
|
'IE'
|
'Z'
|
1
|
100.00%
|
1
|
||||
'LT'
|
'A'
|
5.3335
|
0.49%
|
3
|
0.27%
|
1087.4155
|
99.24%
|
1095.749
|
'LT'
|
'D2'
|
1
|
100.00%
|
1
|
||||
'LT'
|
'F'
|
1
|
100.00%
|
1
|
||||
'LT'
|
'W'
|
2
|
2.53%
|
76.9998
|
97.47%
|
78.9998
|
||
'LT'
|
'LU'
|
21.0664
|
0.18%
|
11023
|
95.18%
|
536.9265
|
4.64%
|
11580.9929
|
'LT'
|
'A'
|
21.0664
|
0.18%
|
10975
|
95.16%
|
536.9265
|
4.66%
|
11532.9929
|
'LT'
|
'D2'
|
47
|
100.00%
|
47
|
||||
'LT'
|
'F'
|
1
|
100.00%
|
1
|
||||
'LV'
|
'A'
|
1.75
|
0.11%
|
43
|
2.62%
|
1595.2447
|
97.27%
|
1639.9947
|
'LV'
|
'D'
|
9.9997
|
100.00%
|
9.9997
|
||||
'LV'
|
'W'
|
1
|
0.95%
|
103.9993
|
99.05%
|
104.9993
|
||
'MT'
|
'A'
|
18
|
100.00%
|
18
|
||||
'NL'
|
'A'
|
240.0364
|
0.12%
|
180555
|
88.79%
|
22559.8263
|
11.09%
|
203354.8627
|
'NL'
|
'B'
|
19.7777
|
3.39%
|
563
|
96.61%
|
582.7777
|
||
'NL'
|
'C'
|
3
|
0.08%
|
3528
|
99.86%
|
2
|
0.06%
|
3533
|
'NL'
|
'D'
|
8.3213
|
0.19%
|
858
|
19.22%
|
3598.1583
|
80.60%
|
4464.4796
|
'NL'
|
'D2'
|
3103
|
100.00%
|
3103
|
||||
'NL'
|
'F'
|
1
|
100.00%
|
1
|
||||
'NL'
|
'T'
|
1
|
100.00%
|
1
|
||||
'NL'
|
'W'
|
57.7433
|
0.54%
|
156
|
1.47%
|
10410.1375
|
97.99%
|
10623.8808
|
'PL'
|
'A'
|
455.6585
|
1.04%
|
1527
|
3.49%
|
41791.1637
|
95.47%
|
43773.8222
|
'PL'
|
'D2'
|
1707
|
100.00%
|
1707
|
||||
'PL'
|
'E'
|
205
|
100.00%
|
205
|
||||
'PL'
|
'F'
|
21
|
100.00%
|
21
|
||||
'PL'
|
'U'
|
67.9997
|
0.91%
|
156
|
2.08%
|
7275.9663
|
97.01%
|
7499.966
|
'PL'
|
'W'
|
43.3667
|
3.68%
|
9
|
0.76%
|
1126.6265
|
95.56%
|
1178.9932
|
'PT'
|
'A'
|
2.6665
|
0.13%
|
225
|
10.74%
|
1867.321
|
89.13%
|
2094.9875
|
'PT'
|
'D'
|
1
|
100.00%
|
1
|
||||
'PT'
|
'D2'
|
17
|
100.00%
|
17
|
||||
'PT'
|
'F'
|
3
|
100.00%
|
3
|
||||
'PT'
|
'T'
|
4.8666
|
0.02%
|
561
|
2.21%
|
24796.2286
|
97.77%
|
25362.0952
|
'PT'
|
'U'
|
1
|
0.11%
|
137
|
15.46%
|
747.9962
|
84.42%
|
885.9962
|
'PT'
|
'W'
|
2.8779
|
0.73%
|
391.1207
|
99.27%
|
393.9986
|
||
'RO'
|
'A'
|
0.2
|
0.01%
|
199
|
5.13%
|
3683.5966
|
94.87%
|
3882.7966
|
'RO'
|
'D2'
|
958
|
100.00%
|
958
|
||||
'RO'
|
'F'
|
1
|
100.00%
|
1
|
||||
'RO'
|
'W'
|
4.5
|
2.27%
|
2
|
1.01%
|
191.5007
|
96.72%
|
198.0007
|
'SE'
|
'A'
|
13583.6057
|
2.65%
|
496334
|
96.88%
|
2401.1067
|
0.47%
|
512318.7124
|
'SE'
|
'B'
|
1
|
2.56%
|
4
|
10.26%
|
33.9995
|
87.18%
|
38.9995
|
'SE'
|
'C'
|
177719
|
100.00%
|
177719
|
||||
'SE'
|
'D'
|
16943.7616
|
65.38%
|
6173
|
23.82%
|
2799.8962
|
10.80%
|
25916.6578
|
'SE'
|
'D2'
|
15114
|
100.00%
|
15114
|
||||
'SE'
|
'F'
|
382
|
100.00%
|
382
|
||||
'SE'
|
'W'
|
1756.8636
|
7.37%
|
125
|
0.52%
|
21971.9091
|
92.11%
|
23853.7727
|
'SI'
|
'A'
|
0.5
|
0.02%
|
120
|
4.19%
|
2740.485
|
95.79%
|
2860.985
|
'SI'
|
'D2'
|
12
|
100.00%
|
12
|
||||
'SI'
|
'F'
|
5
|
100.00%
|
5
|
||||
'SI'
|
'T'
|
3.8
|
0.04%
|
47
|
0.51%
|
9091.1778
|
99.44%
|
9141.9778
|
'SI'
|
'W'
|
8.551
|
1.77%
|
5
|
1.04%
|
468.2801
|
97.19%
|
481.8311
|
'SK'
|
'A'
|
1.7333
|
0.03%
|
153
|
2.49%
|
5991.1006
|
97.48%
|
6145.8339
|
'SK'
|
'D'
|
5
|
71.43%
|
2.0003
|
28.57%
|
7.0003
|
||
'SK'
|
'D2'
|
28
|
100.00%
|
28
|
||||
'SK'
|
'F'
|
1
|
100.00%
|
1
|
||||
'SK'
|
'U'
|
3
|
0.27%
|
141
|
12.50%
|
984.1595
|
87.24%
|
1128.1595
|
'SK'
|
'W'
|
14.8334
|
5.97%
|
2
|
0.81%
|
231.4441
|
93.22%
|
248.2775
|
TOTAL
|
314013.3397
|
6.18%
|
2764585
|
54.41%
|
2001983.501
|
39.40%
|
5080581.841
|
|
APPENDIX A
Faster SQL
code for query 1
Alter table
patstat.tls201_appln add index t1(appln_auth);
Drop table if exists
nationalweights;
Drop table if exists
nationalcounts;
Create table
nationalweights
select appln_id, 1/count(person_id)
as weight
From patstat.tls207_pers_appln
Where INVT_SEQ_NR
> 0
Group by appln_id;
Alter table
nationalweights add index t1(appln_id);
Create table
nationalcounts
Select distinct
c.EU27sort,
c.Countrycode,
t01.APPLN_AUTH,
t01.APPLN_KIND,
t07.APPLN_ID,
t07.person_id,
if(t06.PERSON_CTRY_CODE = '',
"N", "Y") as CYDATA,
a.weight
From
patstat.tls201_appln t01 Inner Join
patstat.tls207_pers_appln t07 On t01.APPLN_ID
= t07.APPLN_ID Inner Join
patstat.tls206_person
t06 On t07.PERSON_ID = t06.PERSON_ID Inner Join
rdk.countries c On c.ISO3166 =
t01.APPLN_AUTH inner join
nationalweights as a
on a.appln_id = t01.appln_id
Where
c.EU27sort > 0 And
year(t01.appln_filing_date)
>=2000 and
t07.INVT_SEQ_NR > 0;
Alter table
patstat.tls201_appln drop index t1;
Faster SQL
code for query 2
Drop table if exists
t07;
Drop table if exists
nationalcounts_v;
Create table t07
select *
from
patstat.tls207_pers_appln
where
INVT_SEQ_NR > 0;
Alter table t07 add
index t1(appln_id);
create table
tazza.nationalcounts_v
Select distinct
c.EU27sort,
c.Countrycode,
t01.APPLN_AUTH,
t01.APPLN_KIND,
t01.APPLN_ID,
"V" as CYDATA,
1 as weight
From
patstat.tls201_appln t01
left Join t07
On t01.APPLN_ID = t07.APPLN_ID
Inner Join rdk.countries c
On c.ISO3166 = t01.APPLN_AUTH
Where
c.EU27sort > 0
and
year(t01.appln_filing_date) >=2000
and t07.APPLN_ID is
null;
Insert into
nationalcounts
SELECT n.`EU27sort`,
n.`Countrycode`, n.`APPLN_AUTH`, n.`APPLN_KIND`, n.`APPLN_ID`,
0 as person_id,
n.`CYDATA`, n.`weight` FROM tazza.nationalcounts_v n;
No comments:
Post a Comment