主页 / user-1543303

Gery's questions

Martin Hope
Gery
Asked: 2025-04-27 01:32:50 +0800 CST
  • 5

在搜索了这个问题之后，不确定是否真的可以这样做，感谢您的指点。

我的观点是这样创建的：

create or replace view OutputView as
select * from view1
natural full outer join view2;

使用：

update OutputView set colA = 'undefined' where colA is null;

给出：

ERROR:  cannot update view "OutputView"
DETAIL:  Views that do not select from a single table or view are not automatically updatable.
HINT:  To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.

到目前为止，该触发器和规则还没有成功。问题在于输出视图中的某些列具有空值，因为这些列在原始视图中不存在，例如：

view1
-----
colA colB
1    a
2    b

view2
-----
colB colC
100  x
200  y

OutputView
----------
colA  colB  colC
1     a     null
2     b     null
null  100   x
null  200   y
postgresql
Martin Hope
Gery
Asked: 2024-08-03 11:46:39 +0800 CST
  • 5

虽然这看起来是一个简单的任务，但我却很难打印出经过这样过滤的几个文件的内容：

$ ls *_pattern_*
a_pattern_a
b_pattern_b
c_pattern_c
total_pattern_total

ls *_pattern_* | grep -v total
a_pattern_a
b_pattern_b
c_pattern_c

ls *_pattern_* | grep -v total | xargs cat
cat: 'a_pattern_a': No such file or directory
cat: 'b_pattern_b': No such file or directory
cat: 'c_pattern_c': No such file or directory

也许我需要再买点咖啡...任何提示都非常感谢。

linux
Martin Hope
Gery
Asked: 2024-06-03 05:35:54 +0800 CST
  • 6

使用此链接我可以计算与多边形相交的所有点：

ogrinfo -sql "select count(*) from tmppol, tmppoi where st_intersects(tmppol.geometry,tmppoi.geometry) group by tmppol.fid" -dialect sqlite input.vrt

我的疑问是如何修改它以获得所有点值的总和并将结果放入多边形形状文件的列中？也许作为更新调用，例如：

ogrinfo tmppol.shp -dialect sqlite -sql "update tmppol set sum = sum(tmpoi.tn) from tmppol where st_intersects(tmppol.geom, tmppoi.geom) ???"

非常感谢任何提示，

MWE 文件可以从这里下载。

sql
Martin Hope
Gery
Asked: 2024-05-30 01:19:49 +0800 CST
  • 6

我有一个包含 >19 000 行的文件，其结构如下：

$ head -10 a_vt
9999.77,-83.03,-7.71771771772,276.97,-7.71771771772
9999.48,-83.57,-7.23723723724,276.43,-7.23723723724
9999.08,-83.99,-7.2972972973,276.01,-7.2972972973
9998.75,-81.71,-6.996996997,278.29,-6.996996997
9998.75,-81.65,-6.996996997,278.35,-6.996996997
9998.69,-80.87,-8.7987987988,279.13,-8.7987987988
9998.34,-81.05,-8.43843843844,278.95,-8.43843843844
9997.89,-83.99,-6.21621621622,276.01,-6.21621621622
9997.77,-77.27,-16.1261261261,282.73,-16.1261261261
9997.54,-82.43,-4.29429429429,277.57,-4.29429429429
...
...

并使用这种类型的文件（即通常具有可变行数的文件）：

$ cat b_vm
22850,39.78686TN,39.78686TN,-75.6259,-14.9867,284.374,-14.9867
22901.9,9.90099TN,9.90099TN,-75.649,-14.9636,284.351,-14.9636
27742.2,160.0TN,160.0TN,-75.5999,-14.9922,284.4,-14.9922
22901.9,110.0TN,110.0TN,-75.6648,-14.9526,284.335,-14.9526
9998.69,110.0TN,110.0TN,-75.6551,-14.9496,284.345,-14.9496
9998.34,100.0TN,100.0TN,-75.62949999999998,-14.9573,284.37,-14.9573
27742.2,90.0TN,90.0TN,-75.60129999999998,-14.9973,284.399,-14.9973
27685.3,90.0TN,90.0TN,-75.6024,-14.9626,284.398,-14.9626
27742.2,80.0TN,80.0TN,-75.6014,-15.0006,284.399,-15.0006
22901.9,80.0TN,80.0TN,-75.6597,-14.9626,284.34,-14.9626

匹配第一列的值后，将 19k 行文件过滤掉，以获得：

$ awk 'NR==FNR { a[$1]; next }( ($1 in a) ) { print }' FS="," b_vm a_vt 
9998.69,-80.87,-8.7987987988,279.13,-8.7987987988
9998.34,-81.05,-8.43843843844,278.95,-8.43843843844

我不是awk这里的专家，但我知道这个单行 awk 调用允许在比较两个文件中第一列的值后从缓存中获取值。问题是打印第二列b_vmt以及如下：

9998.69,-80.87,-8.7987987988,279.13,-8.7987987988,**110.0TN**
9998.34,-81.05,-8.43843843844,278.95,-8.43843843844,**100.0TN**

任何提示都非常受欢迎，

bash