I am not sure about the rank SQL, but my SQL was
wrong. It is close to SQL for "One product" but still
would require a product parameter.
Here's hopefully correct examples. The key here is
designating good test data sets.
NB. =========================================================
PR=: ;:'P1 P1 P2 P2 P3 P3 P3'
SM=: ;:'S11 S12 S21 S22 S31 S32 S33'
DT=: 11 12 11 30 12 22 31
Note 'DDL'
create x (
PR varchar(32),
SM varchar(32),
DT date
);
)
NB. =========================================================
Note 'One product'
select PR,SM,DT
from x
where DT = (select max(DT) from x
where DT <= @d and PR = @p)
p=. <'P3'
d=. 25
i=. DT i. >./ ((PR=p) *. (DT<:d)) # DT
(>i{PR) ; (>i{SM) ; (,.i{DT)
+--+---+--+
|P3|S32|22|
+--+---+--+
)
NB. =========================================================
Note 'List of products'
select x.PR,x.SM,x.DT
from x, (select PR,max(DT) MD from x
where DT <= @d and PR in @p
group by PR) y
where x.PR=y.PR and x.DT=y.MD
p=. ;:'P1 P2'
d=. 25
]i=. (PR e. p) *. (DT <: d)
1 1 1 0 0 0 0
(>~.i#PR) ; (,.(i#PR) >.//. i#DT)
+--+--+
|P1|12|
|P2|11|
+--+--+
(>~.i#PR) ; (,.(i#PR) >.//. i#DT) ; (":@>(i#PR) </. i#i.#DT)
+--+--+---+
|P1|12|0 1|
|P2|11|2 |
+--+--+---+
]j=. (i#PR) ({~ (i#DT) i. [: >./ {&(i#DT))/. i#i.#DT
1 2
(>j{PR) ; (>j{SM) ; (,.j{DT)
+--+---+--+
|P1|S12|12|
|P2|S21|11|
+--+---+--+
)
NB. =========================================================
--- Sashikanth Chandrasekaran <[EMAIL PROTECTED]> wrote:
> The query below uses a join (self-join). You can use
> SQL99 RANK() functions if you want to avoid the
> self-join.
>
> For e.g., if you have Oracle 9i or above:
>
> create table x(symbol varchar2(30),
> prodid varchar2(30),
> mydate date);
>
> select symbol, prodid
> from (select symbol, prodid,
> RANK() OVER (PARTITION BY symbol
> ORDER BY mydate DESC)
> as myrank
> from x
> where mydate <= ...)
> where myrank = 1;
>
>
>
> [Jprogramming] SQL equivalent of J function
> Oleg Kobchenko olegykj at yahoo.com wrote:
>
> > You don't need to join tables, since x has both >
> fields.
>
> > create table x (
> > symbol varchar,
> > prodid varchar,
> > date date
> > );
>
> > -- select mapping effective on given date
>
> > select symbol,prodid
> > from x
> > where date = (select max(date) from x where date <=
> ?);
>
>
> --- Dan Bron <jf at dbron.net> wrote:
>
> > I need some help with SQL.
> >
> > I have a set of stock symbols and a set of internal
> product IDs which map 1-to-1 to those
> > symbols. Sometimes the mapping changes (for
> example, when the symbol changes its meaning on the
> > exchange).
> >
> > So I have a SQL table that tells me the symbol, the
> product ID, and the date. This table was
> > initialized with all the symbols and their first
> product IDs, and a new row is added with
> > symbol, (new) product_id, and date whenever a
> mapping changes.
> >
> > What I want is, for a given set of symbols, and a
> particular date, the product ID for that
> > symbol as of that date.
> >
> > Obviously, the mapping for a particular symbol may
> changed 0 or more times on or before that
> > date, so the table will have 1 or more symbol,
> product_id, date entries for each symbol.
> >
> > In J, a model of the function I want is:
> >
> >
> > map_it =: verb define
> > filter =. (symbol__x e. symbol__y) *.
> date__x <: some_date
> >
> > rows =. filter # prod_id__x ,. date__x
> >
> > prod_ids =. ({.@:/:/ @: |:/.~ {."1)
> rows
> >
> > symbol__y ,. prod_ids
> > )
> >
> > NB. {.@:/: lots slower than {~ (i.
> >./)@:;
> >
> >
> > where the arguments to the verbs are two objects
> (locales) representing the tables and, e.g.
> > symbol__x is the symbol column from the left table.
> >
> > What I need is the SQL equivalent. I made the
> follow attempt, but it doesn't work:
> >
> >
> > SELECT
> > TOP 1
> > y.symbol,
> > x.prod_id,
> >
> > FROM
> > x,
> > y
> >
> > WHERE
> > x.date <= @some_date
> > AND
> > x.symbol = y.symbol
> >
> > GROUP BY
> > y.symbol
> >
> > ORDER BY
> > x.date
> > DESC
> >
> >
> > the SQL engine complains that for each column I want
> in the output, I need to either include it
> > in the GROUP BY clause, or use an aggregation
> function on it in the SELECT clause. Apparently
> > TOP 1 doesn't count as an aggregation function.
> >
> > Suggestions?
> >
> > -Dan
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
> ----------------------------------------------------------------------
> For information about J forums see http://www.jsoftware.com/forums.htm
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
