sql - 从复杂的选择创建 View

Question

我有一个非常复杂的查询，我想从中创建一个 View 。查询如下所示:

select s.avg as c3, fs.bayes, fs.sure, fs.visu, fs.fstd from 
(
SELECT  AVG(q.c3), COUNT(q.c3), q.std
FROM    (
        SELECT  std, c3, ROW_NUMBER() OVER (PARTITION BY std ORDER BY id) AS rn
        FROM    ssims
        WHERE   obraz = 'peppers2' and noisetype ='L' and data>'2009-12-23' and maska = 9
        ) q
WHERE   rn <= 15
GROUP BY
        std
        ) s
,(
SELECT  AVG(f.bayes) as bayes, AVG(f.sure) as sure, AVG(f.visu) as visu, COUNT(f.bayes) as fcount, f.std as fstd
FROM    (
        SELECT  std, bayes, sure, visu, ROW_NUMBER() OVER (PARTITION BY std ORDER BY id) AS rn
        FROM    falki_ssim
        WHERE   obraz = 'peppers2' and noisetype ='L'
        ) f
WHERE   rn <= 15
GROUP BY
        std
        ) fs
where s.std = fs.fstd

它选择我使用指定参数的测试的平均结果。此查询的输出返回 10 行(std 值从 5 到 50，步骤 5，所以 5、10、15...)和 5 列:c3、bayes、sure、visu、std

但我希望能够更改两个子查询的 obraz 和 noisetype 字段。所以在创建这样的 View 之后，我希望能够以这种方式选择结果:

select * from my_view where obraz='peppers2' and noisetype = 'L'

怎么做？

Answer 1

我必须对其进行测试以获得准确的查询，但基本思想是在您的选择查询和分组依据子句中包含 obraz 和 noisetype。像这样:

select s.obraz, s.noisetype, s.avg as c3, fs.bayes, fs.sure, fs.visu, fs.fstd from 
(
SELECT  obraz, noisetype, AVG(q.c3), COUNT(q.c3), q.std
FROM    (
        SELECT  obraz, noisetype, std, c3, ROW_NUMBER() OVER (PARTITION BY obraz, noisetype, std ORDER BY id) AS rn
        FROM    ssims
        WHERE   data>'2009-12-23' and maska = 9
        ) q
WHERE   rn <= 15
GROUP BY
        obraz, noisetype, std
        ) s
,(
SELECT  obraz, noisetype, AVG(f.bayes) as bayes, AVG(f.sure) as sure, AVG(f.visu) as visu, COUNT(f.bayes) as fcount, f.std as fstd
FROM    (
        SELECT  obraz, noisetype, std, bayes, sure, visu, ROW_NUMBER() OVER (PARTITION BY obraz, noisetype, std ORDER BY id) AS rn
        FROM    falki_ssim
        ) f
WHERE   rn <= 15
GROUP BY
        obraz, noisetype, std
        ) fs
where s.std = fs.fstd AND s.obraz = fs.obraz AND s.noisetype = fs.noisetype

您还应该使用 JOIN 关键字来连接表。