Обсуждение: complex queries

Поиск

Список

Период

Сортировка

complex queries

От

Kumar S

Дата:

11 марта 2005 г., 16:45:38

Hi Sean and group members,

with all your help i could upload a part of my dataset
successfully into my database.

I am queying my database. currently I am querying two
tables for data.

There are two tables processed_data and
sequence_annotation.


My first query (which is successful):

db=> select proc_exprs, proc_pval from processed_data,
sequence_annotation
where processed_data.seq_anno_id =
sequence_annotation.seq_anno_id
and sequence_annotation.seq_gene_symbol = 'FN1';
 proc_exprs | proc_pval
------------+-----------
    6.72867 |  0.141216
    5.01667 |  0.212055
    4.94133 |  0.216501
   0.214667 |  0.624944
  0.0846667 |  0.525513
    7.19867 |  0.128558
    11.7807 |  0.053066
    11.5487 | 0.0545418
    7.57867 | 0.0984488
       0.33 |  0.678788
  0.0546667 |  0.483358
    11.7227 | 0.0534283
      4.806 |  0.221026
    2.86333 |  0.434024
    3.60933 |  0.324803
       0.43 |  0.742388
      0.018 |  0.427739
      3.222 |  0.375615
(18 rows)


Query 2: A little complicated one (successful):

db=> select proc_exprs, proc_pval, seq_anno_id from
processed_data
 where proc_pval < 0.004;
 proc_exprs | proc_pval  | seq_anno_id
------------+------------+-------------
    107.803 | 0.00382485 |       50237
    106.607 | 0.00387695 |       51903
    158.191 | 0.00240939 |       69829
    170.103 | 0.00220903 |       70008
    137.591 | 0.00284852 |       70009
    217.086 | 0.00165253 |       70214
    186.021 | 0.00198545 |       70223
    213.575 | 0.00168479 |       70558
    121.545 | 0.00330776 |       71717
    221.715 | 0.00161171 |       71757
    248.226 |  0.0014101 |       75187
    111.929 | 0.00365456 |       80492
      138.5 |  0.0022871 |       70008
    90.9887 | 0.00380519 |       70009

Query 3: A complicated one( unsuccessful):

bob=> select proc_exprs, proc_pval, seq_gene_symbol
from processed_data, sequence_annotation
where proc_pval < 0.05 AND
processed_data.seq_anno_id =
sequence_annotation.seq_gene_symbol;


 proc_exprs | proc_pval | seq_gene_symbol
------------+-----------+-----------------
(0 rows)


seq_gene_symbol is in sequence_annotation table and
its primary key is value of seq_anno_id.


Why I am getting 0 row. Whats wrong with my query.

Could you help me please.

Thank you.

K


My tables:
Processed_data table:
   Column    |          Type          |
              Modifiers
-------------+------------------------+---------------------------------------------------------------------
 proc_id     | integer                | not null
default
nextval('public.processed_data_proc_id_seq'::text)
 exp_id      | integer                |
 seq_anno_id | integer                |
 cel_id      | integer                |
 proc_symbol | character varying(100) |
 proc_exprs  | real                   |
 proc_pval   | real                   |
Indexes:
    "processed_data_pkey" primary key, btree (proc_id)
Foreign-key constraints:
    "$2" FOREIGN KEY (exp_id) REFERENCES
experiment(exp_id)
    "$3" FOREIGN KEY (seq_anno_id) REFERENCES
sequence_annotation(seq_anno_id)
    "$4" FOREIGN KEY (cel_id) REFERENCES cel(cel_id)



Sequence_annotation table:
       Column         |           Type           |
                             Modifiers

-----------------------+--------------------------+------------------------------------------------------------------------------
 seq_anno_id           | integer                  |
not null default
nextval('public.sequence_annotation_seq_anno_id_seq'::text)
 seq_probeset_id       | character varying(15000) |
 seq_source            | character varying(15000) |
 seq_transcript_id     | character varying(15000) |
 seq_target_desc       | character varying(15000) |
 seq_rep_pub_id        | character varying(15000) |
 seq_unigene_id        | character varying(15000) |
 seq_alignment         | character varying(15000) |
 seq_gene_title        | character varying(15000) |
 seq_gene_symbol       | character varying(15000) |
 seq_chr_location      | character varying(15000) |
 seq_ensembl           | character varying(15000) |
 seq_locus_link        | character varying(15000) |
 seq_swissport         | character varying(15000) |
 seq_ec                | character varying(15000) |
 seq_omim              | character varying(15000) |
 seq_refseq_prot_id    | character varying(15000) |
 seq_refseq_trans_id   | character varying(15000) |
 seq_flybase           | character varying(15000) |
 seq_agi               | character varying(15000) |
 seq_wormbase          | character varying(15000) |
 seq_mgi_name          | character varying(15000) |
 seq_rgd_name          | character varying(15000) |
 seq_sgd_acc           | character varying(15000) |
 seq_go_bp             | character varying(15000) |
 seq_go_cc             | character varying(15000) |
 seq_go_mf             | character varying(15000) |
 seq_pathway           | character varying(15000) |
 seq_prot_family       | character varying(15000) |
 seq_prot_domain       | character varying(15000) |
 seq_interpro_id       | character varying(15000) |
 seq_tm                | character varying(15000) |
 seq_qtl               | character varying(15000) |
 seq_ann_trasc_cluster | character varying(15000) |
 seq_transc_assign     | character varying(15000) |
 seq_anno_notes        | character varying(15000) |
 genechip_id           | integer                  |
Indexes:
    "sequence_annotation_pkey" primary key, btree
(seq_anno_id)
Foreign-key constraints:
    "$1" FOREIGN KEY (genechip_id) REFERENCES
genechip(genechip_id)



__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Re: complex queries

От

Sean Davis

Дата:

11 марта 2005 г., 17:53:15

On Mar 11, 2005, at 11:45 AM, Kumar S wrote:

> bob=> select proc_exprs, proc_pval, seq_gene_symbol
> from processed_data, sequence_annotation
> where proc_pval < 0.05 AND
> processed_data.seq_anno_id =
> sequence_annotation.seq_gene_symbol;
>
>
>  proc_exprs | proc_pval | seq_gene_symbol
> ------------+-----------+-----------------
> (0 rows)
>
>
> seq_gene_symbol is in sequence_annotation table and
> its primary key is value of seq_anno_id.
>

You probably need to spend some time reading about queries in an SQL
book (many of which are online), but in this particular case, it
doesn't look like seq_gene_symbol is going to equal seq_anno_id.  It
looks like you want:

processed_data.seq_anno_id=sequence_annotation.seq_anno_id

Off-topic, but have you thought about looking at using BASE or CHADO to
keep your microarray data?

Sean

Re: complex queries

От

"Todd Lewis"

Дата:

12 марта 2005 г., 20:54:05

It's very hard to tell exactly which table your columns are coming
from in your SQL. It makes it cleaner and more readable if you alias
your table.

select
 pd.proc_exprs
, pd.proc_pval
, sa.seq_gene_symbol

from
processed_data pd   -- pd is and alias name for processed_data
, sequence_annotation sa

where
pd.seq_anno_id = sa.seq_gene_symbol
AND pd.proc_pval < 0.05;

don't depend upon the query to recognize that the column you are
stating is unique between all the tables selected. Plus using a short
alias that makes sense makes your query more easily readable.

As you start making more complex queries these tips will help.
1. use alias table names in your query.
2. when you have multple tables define the relations at the beginning
of the where clause. Then define your constraint.
3. Try do define your relationships starting with the larger tables.
This speeds up processing of the query.

You can take some real performance hits when your query uses 12
tables.

> Hi Sean and group members,
>
> with all your help i could upload a part of my dataset
> successfully into my database.
>
> I am queying my database. currently I am querying two
> tables for data.
>
> There are two tables processed_data and
> sequence_annotation.
>
>
> My first query (which is successful):
>
> db=> select proc_exprs, proc_pval from processed_data,
> sequence_annotation
> where processed_data.seq_anno_id =
> sequence_annotation.seq_anno_id
> and sequence_annotation.seq_gene_symbol = 'FN1';
>  proc_exprs | proc_pval
> ------------+-----------
>     6.72867 |  0.141216
>     5.01667 |  0.212055
>     4.94133 |  0.216501
>    0.214667 |  0.624944
>   0.0846667 |  0.525513
>     7.19867 |  0.128558
>     11.7807 |  0.053066
>     11.5487 | 0.0545418
>     7.57867 | 0.0984488
>        0.33 |  0.678788
>   0.0546667 |  0.483358
>     11.7227 | 0.0534283
>       4.806 |  0.221026
>     2.86333 |  0.434024
>     3.60933 |  0.324803
>        0.43 |  0.742388
>       0.018 |  0.427739
>       3.222 |  0.375615
> (18 rows)
>
>
> Query 2: A little complicated one (successful):
>
> db=> select proc_exprs, proc_pval, seq_anno_id from
> processed_data
>  where proc_pval < 0.004;
>  proc_exprs | proc_pval  | seq_anno_id
> ------------+------------+-------------
>     107.803 | 0.00382485 |       50237
>     106.607 | 0.00387695 |       51903
>     158.191 | 0.00240939 |       69829
>     170.103 | 0.00220903 |       70008
>     137.591 | 0.00284852 |       70009
>     217.086 | 0.00165253 |       70214
>     186.021 | 0.00198545 |       70223
>     213.575 | 0.00168479 |       70558
>     121.545 | 0.00330776 |       71717
>     221.715 | 0.00161171 |       71757
>     248.226 |  0.0014101 |       75187
>     111.929 | 0.00365456 |       80492
>       138.5 |  0.0022871 |       70008
>     90.9887 | 0.00380519 |       70009
>
> Query 3: A complicated one( unsuccessful):
>
> bob=> select proc_exprs, proc_pval, seq_gene_symbol
> from processed_data, sequence_annotation
> where proc_pval < 0.05 AND
> processed_data.seq_anno_id =
> sequence_annotation.seq_gene_symbol;
>
>
>  proc_exprs | proc_pval | seq_gene_symbol
> ------------+-----------+-----------------
> (0 rows)
>
>
> seq_gene_symbol is in sequence_annotation table and
> its primary key is value of seq_anno_id.
>
>
> Why I am getting 0 row. Whats wrong with my query.
>
> Could you help me please.
>
> Thank you.
>
> K
>
>
> My tables:
> Processed_data table:
>    Column    |          Type          |
>               Modifiers
> -------------+------------------------+---------------------------------------------------------------------
>  proc_id     | integer                | not null
> default
> nextval('public.processed_data_proc_id_seq'::text)
>  exp_id      | integer                |
>  seq_anno_id | integer                |
>  cel_id      | integer                |
>  proc_symbol | character varying(100) |
>  proc_exprs  | real                   |
>  proc_pval   | real                   |
> Indexes:
>     "processed_data_pkey" primary key, btree (proc_id)
> Foreign-key constraints:
>     "$2" FOREIGN KEY (exp_id) REFERENCES
> experiment(exp_id)
>     "$3" FOREIGN KEY (seq_anno_id) REFERENCES
> sequence_annotation(seq_anno_id)
>     "$4" FOREIGN KEY (cel_id) REFERENCES cel(cel_id)
>
>
>
> Sequence_annotation table:
>        Column         |           Type           |
>                              Modifiers
>
-----------------------+--------------------------+------------------------------------------------------------------------------
>  seq_anno_id           | integer                  |
> not null default
> nextval('public.sequence_annotation_seq_anno_id_seq'::text)
>  seq_probeset_id       | character varying(15000) |
>  seq_source            | character varying(15000) |
>  seq_transcript_id     | character varying(15000) |
>  seq_target_desc       | character varying(15000) |
>  seq_rep_pub_id        | character varying(15000) |
>  seq_unigene_id        | character varying(15000) |
>  seq_alignment         | character varying(15000) |
>  seq_gene_title        | character varying(15000) |
>  seq_gene_symbol       | character varying(15000) |
>  seq_chr_location      | character varying(15000) |
>  seq_ensembl           | character varying(15000) |
>  seq_locus_link        | character varying(15000) |
>  seq_swissport         | character varying(15000) |
>  seq_ec                | character varying(15000) |
>  seq_omim              | character varying(15000) |
>  seq_refseq_prot_id    | character varying(15000) |
>  seq_refseq_trans_id   | character varying(15000) |
>  seq_flybase           | character varying(15000) |
>  seq_agi               | character varying(15000) |
>  seq_wormbase          | character varying(15000) |
>  seq_mgi_name          | character varying(15000) |
>  seq_rgd_name          | character varying(15000) |
>  seq_sgd_acc           | character varying(15000) |
>  seq_go_bp             | character varying(15000) |
>  seq_go_cc             | character varying(15000) |
>  seq_go_mf             | character varying(15000) |
>  seq_pathway           | character varying(15000) |
>  seq_prot_family       | character varying(15000) |
>  seq_prot_domain       | character varying(15000) |
>  seq_interpro_id       | character varying(15000) |
>  seq_tm                | character varying(15000) |
>  seq_qtl               | character varying(15000) |
>  seq_ann_trasc_cluster | character varying(15000) |
>  seq_transc_assign     | character varying(15000) |
>  seq_anno_notes        | character varying(15000) |
>  genechip_id           | integer                  |
> Indexes:
>     "sequence_annotation_pkey" primary key, btree
> (seq_anno_id)
> Foreign-key constraints:
>     "$1" FOREIGN KEY (genechip_id) REFERENCES
> genechip(genechip_id)
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: complex queries

complex queries

Re: complex queries

Re: complex queries