Sunday, June 17, 2012

EU27 patent authorities inventors country analisys (3/3)

[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;