oracle – Query performance in describing a race’s relative finishing positions

Is there a faster, and more parsimonious way to do this?

For each race (i.e. 1, 2, and 3), each row contains a race entry X (which is a horse), some details including it’s finishing position, and in the same row the best 3 other entries not including X (transposing relative non aggregate data from other rows into columns). So say X was came 1st in the race, the best relative finishing position would be 2nd and the next best would be 3rd, but if X’s came 2nd, the best relative finishing position would be 1st and the next best would be 3rd.

Note, I’ve tried putting case statements in joins – but the performance was bad. There are PKs and indexes on joins.

So here is my skeleton query (the actual query is huge with a lot more inline views in case statements, and > 1 million rows). The driver table is race_entry.. the rownum checks are there to guard against bad data (2 or more horses finishing 1st, who would have thunk). Many thanks in advance!

select 
      re.race_entry_code  -- pk / unique identifier
    , r.race_code -- (i.e. 1, 2 and 3)
    , h.horse_code -- (entry X)
    , h.name as horse_name
    , re.position_finishing_code

    -- relative best entries 1 
    , case when re.position_finishing_code = 1 
        then (select bh.horse_code from race_entry bh where bh.race_code=re.race_code and bh.position_finishing_code=2  and rownum<=1)
        else (select bh.horse_code from race_entry bh where bh.race_code=re.race_code and bh.position_finishing_code=1  and rownum<=1) 
           end as BEST_HORSE_CODE
    , case when re.position_finishing_code = 1  
                then (select  hi.name from race_entry bh join horse hi on bh.horse_code=hi.horse_code where bh.race_code=re.race_code and bh.position_finishing_code=2  and rownum<=1) 
            else (select hi.name from race_entry bh join horse hi on bh.horse_code=hi.horse_code where bh.race_code=re.race_code and bh.position_finishing_code=1  and rownum<=1) 
      end as BEST_HORSE_NAME
    , case when re.position_finishing_code=1  then (select bh.position_finishing_code from race_entry bh    where bh.race_code=re.race_code and bh.position_finishing_code=2  and rownum<=1) 
            else (select bh.position_finishing_code from race_entry bh where bh.race_code=re.race_code and bh.position_finishing_code=1  and rownum<=1) 
      end as BEST_HORSE_POSITION
      
    -- relative best entries 2 
    , case when re.position_finishing_code between 1 and 2 
        then (select bh.horse_code from race_entry bh where bh.race_code=re.race_code and bh.position_finishing_code=3  and rownum<=1)
        else (select bh.horse_code from race_entry bh where bh.race_code=re.race_code and bh.position_finishing_code=2  and rownum<=1) 
           end as BEST_HORSE_CODE_2
    , case when re.position_finishing_code between 1 and 2  
                then (select  hi.name from race_entry bh join horse hi on bh.horse_code=hi.horse_code where bh.race_code=re.race_code and bh.position_finishing_code=3  and rownum<=1) 
            else (select hi.name from race_entry bh join horse hi on bh.horse_code=hi.horse_code where bh.race_code=re.race_code and bh.position_finishing_code=2  and rownum<=1) 
      end as BEST_HORSE_NAME_2
    , case when re.position_finishing_code between 1 and 2  then (select bh.position_finishing_code from race_entry bh  where bh.race_code=re.race_code and bh.position_finishing_code=3  and rownum<=1) 
            else (select bh.position_finishing_code from race_entry bh where bh.race_code=re.race_code and bh.position_finishing_code=2  and rownum<=1) 
      end as BEST_HORSE_POSITION_2
      
    -- relative best entries 3 
    , case when re.position_finishing_code between 1 and 3 
        then (select bh.horse_code from race_entry bh where bh.race_code=re.race_code and bh.position_finishing_code=4  and rownum<=1)
        else (select bh.horse_code from race_entry bh where bh.race_code=re.race_code and bh.position_finishing_code=3  and rownum<=1) 
           end as BEST_HORSE_CODE_3
    , case when re.position_finishing_code between 1 and 3  
                then (select  hi.name from race_entry bh join horse hi on bh.horse_code=hi.horse_code where bh.race_code=re.race_code and bh.position_finishing_code=4  and rownum<=1) 
            else (select hi.name from race_entry bh join horse hi on bh.horse_code=hi.horse_code where bh.race_code=re.race_code and bh.position_finishing_code=3  and rownum<=1) 
      end as BEST_HORSE_NAME_3
    , case when re.position_finishing_code between 1 and 3  then (select bh.position_finishing_code from race_entry bh  where bh.race_code=re.race_code and bh.position_finishing_code=4  and rownum<=1) 
            else (select bh.position_finishing_code from race_entry bh where bh.race_code=re.race_code and bh.position_finishing_code=3  and rownum<=1) 
      end as BEST_HORSE_POSITION_3

from race_entry re
join race r on re.race_code=r.race_code 
join horse h on re.horse_code=h.horse_code
--where re.race_code=1
order by re.race_code, re.position_finishing_code asc    

query performance – Postgresql update slow on a relative small/medium table

I’m trying to run an update on a table with 10 million rows (I just need to update a few), but the query is taking more time than I would like.

This is the table deinifition:

CREATE TABLE det_price_list
    (
      price_list_id     BIGINT NOT NULL
        CONSTRAINT fk_det_list_precio_venta_2
        REFERENCES price_list
        ON UPDATE RESTRICT ON DELETE RESTRICT,

      det_price_list_id BIGINT NOT NULL,
      product_id        VARCHAR(25),
      account_id        BIGINT,
      price             NUMERIC(22, 8),
      currency_id       BIGINT,
      last_updated      TIMESTAMP,
      
      CONSTRAINT pk_det_price_list
      PRIMARY KEY (price_list_id, det_price_list_id),
      CONSTRAINT fk_det_list_precio_venta_1
      FOREIGN KEY (product_id, account_id) REFERENCES product
      ON UPDATE RESTRICT ON DELETE RESTRICT,
      CONSTRAINT fk_det_price_list_4
      FOREIGN KEY (currency_id, account_id) REFERENCES currency
    );

With this index:

CREATE UNIQUE INDEX indice_det_lista_item
  ON det_price_list (product_id, price_list_id);

CREATE INDEX ix_det_price_list_5
  ON det_price_list (price_list_id, account_id, product_id);

CREATE INDEX ix_det_price_list_1
  ON det_price_list (price_list_id);

CREATE INDEX ix_det_price_list_2
  ON det_price_list (product_id);

CREATE INDEX ix_det_price_list_6
  ON det_price_list (account_id, product_id, price_list_id, last_updated);

CREATE INDEX ix_det_price_list_3
  ON det_price_list (account_id);

CREATE INDEX ix_det_price_list_4
  ON det_price_list (last_updated);

CREATE INDEX det_price_list_account_id_price_list_id_currency_id_i
  ON det_price_list (account_id, price_list_id, currency_id);

And this is the query:

update det_price_list d
    set last_updated = current_timestamp
  where d.account_id = 994
  AND   price_list_id = 4338
  and d.currency_id= 2;

Also here is the query plan using EXPLAIN (ANALYZE, BUFFERS):

QUERY PLAN
Update on det_price_list d  (cost=0.56..219.89 rows=60 width=77) (actual time=1806.823..1806.823 rows=0 loops=1)
  Buffers: shared hit=1040857 read=1704 dirtied=8839
  I/O Timings: read=1.814
  ->  Index Scan using det_price_list_account_id_price_list_id_currency_id_i  on det_price_list d  (cost=0.56..219.89 rows=60 width=77) (actual time=0.034..28.386 rows=26776 loops=1)
        Index Cond: ((account_id  = 994) AND (price_list_id  = 4338) AND (currency_id = 2))
        Buffers: shared hit=2740 dirtied=621
Planning time: 0.157 ms
Execution time: 1806.873 ms

The query is using the index. I’ve already tried to vacumm, vacumm full, reindex and analyze but i can’t reduce that time. I can’t copy into another table and drop the old one because it is not a complete update and also the table is used constantly.

ag.algebraic geometry – Relative affine schemes

I was reading these notes by D. Gaitsgory, and I don’t understand a claim he makes about relative affine schemes. Namely, on page 3 he says that if $f: Y rightarrow X$ is an affine scheme over $X$, then there exist two vector bundles $E_1$, $E_2$ over $X$ together with a map $mathrm{Tot}(E_1) rightarrow mathrm{Tot}(E_2)$ such that $Y simeq mathrm{Tot}(E_1) times_{mathrm{Tot}(E_2)} X$, where $X subset mathrm{Tot}(E_2)$ is the zero section. Here $X$ is projective and $f$ is quasi-projective.

I don’t understand why this description exists. I agree that locally this is true (simply because $f$ is of finite type, hence we have that $f_{ast} mathcal{O}_Y$ is locally a finitely generated algebra – I am assuming $X$ is Noetherian), but I don’t see why one should be able to glue the local presentations to a global one.

Thanks for the help.

windows 10 – How can I copy folder containing Relative Symbolink Link to another drive without replacing Link?

Using Windows 10, I have created a folder named Products in which I add new folders for each product I buy.

On my PC Products folder is similar to

LG.Television
Ryobi.Sprayer
Epson.Scanner
Dell.PC

Then I have created some special folders which names begin with @ and that I use to regroup some products together

On my PC, I have

@Entertainment
@Computer
    Epson.Scanner
    Dell.PC
@Home
@Do-it-yourself

In @Computer, I have created 2 Relative Symbolic Link as visible just above.

This work perfectly as I want. When I click on Dell.PC (Symbolic Link) in @COmputer folder, original Dell.PC folder is correctly open.

BUT when I copy Products folder to another disk for backup purpose using Paste/Copy, all my links are transformed to directories with files. The original directories (and all files in them) are now duplicated in archive folder.

Question:

Using DOS command, how can I do to copy Products directory on another drive KEEPING Symbolic Links ?

geometry – Given distance between cars, how to calculate the relative position and identify if the car is on my left or right?

I am doing decentralized control of vehicles. Assume I am a car with $2$ sensors, sensor $A$ in front of me and sensor $B$ behind. Using this sensors I can measure the distance from me to other vehicles $C,D$ and $E$. Apart from that I don’t have any other information and I cannot communicate with other cars.

Objective:

  1. Calculate the distance between the other cars and their relative position.
  2. To figure out if the other cars are behind or in front of me.
  3. To figure out if the other cars are on my left or right.

Given length of $AC$, I know that finding relative position of car $C$ is a right triangle problem. $AC$ is hypotenuse, I need to find this right triangles legs. All possible right triangles lie in circle with diameter $AC$. However, without defining axis, it is not clear which tringle to choose. So using only sensor $A$ we cannot find the relative position.

enter image description here

Now we draw another circle with diameter $BC$. And now the two circles will intersect at $J$ and that intersection is a point we need to calculate the relative position. Moreover, the intersection of these circles lie on the extension of line $AB$. This is very good, we can use line $AB$ as $y$ axis. Then to find a relative position of car $C$ we need to solve two equations with two unknowns:

begin{align}
AJ^2+JC^2=AC^2\
(AJ+AB)^2+JC^2=BC^2
end{align}

where we assume $AB$ is known in priori.

We can do this for all cars on our right, i.e., $C$ and $D$. We know their relative position and using this information, we can even calculate the distance between them. However, we need to be careful, we need to know if they are in front of us or behind to be able to calculate the distance between them. But that can be checked comparing $AC$ vs $BC$. Since $AC<BC$, we know that car $B$ is on front, and since $AD>BD$, car $D$ is behind. Now we have achieved objectives $1$ and $2$.

However, I have a problem with a left side. I can calculate the car’s $E$ relative position, but I don’t know if it is on my right or left. So I cannot calculate distance $CE$ or $DE$ correctly, there is always two possible answers. Axis $y$ is well defined, comparing the distance from $A$ and $B$ to other cars, we can know if the other car is in $+y$ or $-y$ side. Axis $x$ is not well defined, from distance information we are not able to identify if the car is on our right or left. Is there any smart way to achieve the last objective without adding any additional sensors?

Topology where relative complement of open sets is empty or contains a nonempty open set

Is there a standard name for a topology where every relative complement of an ordered pair of open sets is either empty or contains an open set?


My question is vaguely inspired by this video about Grothendieck topologies. However, my question is much more basic than the contents of the video. I don’t know much about topology or category theory, so please take the summary with a grain of salt; it’s just meant to show where the question comes from.

In the initial example given of an open covering, we have a family of open sets $mathcal{U}$ indexed by an index set $I$ such that $cup_{i in I} mathcal{U}_i = U$. I think we’re insisting on that stronger property rather than $cup_{i in I}mathcal{U}_i subset U$, but I don’t know enough to say for sure. When this example was initially presented, I was wondering how we tell whether a given covering covers a set using machinery given to us by the category alone and not the original topological space.

Then I thought about properties some topologies might have, namely whether the relative complement of two open sets is either empty or contains a non-empty subset.

In the topology ${varnothing, {1}, {0, 1} } $, the relative complement of ${0, 1}$ and ${1}$ is ${0}$, which is not open and has no open subsets except $varnothing$.

In the standard topology on the reals $mathbb{R}$, the relative complement of any ordered pair of open sets is either empty or contains a non-empty open set.

This seems like a really simple property for a topology to have or lack:

  1. Does it have a name?
  2. Does this property work for letting us express whether a given collection of objects is a covering for a particular set in entirely category-theoretic language?

For (2), I think we can argue that we can detect whether any given pair of open sets are disjoint, and therefore whether an open set is pairwise disjoint from a family of open sets. Then we say that if there is an open set $V$ that is a subset of $U$, but is pairwise disjoint from every $mathcal{U}_i$, then $mathcal{U}_i$ is not an open cover. … So we can detect “spillover” in open cover by checking whether each $mathcal{U}_i$ is a subset of $U$ and detect gaps by trying to “fit a pairwise distinct $V$” in between the cracks. I think that works.

path finding – Ponderate dijkstra relative to previous position

I use dijkstra in my rust game to find path to a destination. My game is in a square grid like this:

grid

There is, for illustration, the Rust code which use dijkstra:

use crate::map::Map;
use crate::physics::GridPoint;
use pathfinding::prelude::dijkstra;

pub fn find_path(map: &Map, from: &GridPoint, to: &GridPoint) -> Option<Vec<GridPoint>> {
    match dijkstra(from, |p| map.successors(p), |p| *p == *to) {
        None => None,
        Some(path) => Some(path.0),
    }
}

// ...

pub fn successors(&self, from: &GridPoint) -> Vec<(GridPoint, i32)> {
        let mut successors = vec!();

        for (mod_x, mod_y) in (
            (-1, -1),
            (0, -1),
            (1, -1),
            (-1, 0),
            (0, 0),
            (1, 0),
            (-1, 1),
            (0, 1),
            (1, 1),
        )
        .iter()
        {
            let new_x = from.x + mod_x;
            let new_y = from.y + mod_y;

            if new_x < 0 || new_y < 0 {
                continue;
            }

            if let Some(next_tile) = self.terrain.tiles.get(&(new_x as u32, new_y as u32)) {
                successors.push((GridPoint::new(new_x, new_y), next_tile.pedestrian_cost))
            }
        }

        successors
    }
// ...

And visualization of found path (white is found path, pink is goal):

path

The found path is not a direct line. I’m not surprised, because the weight of diagonal is the same as lateral. With what I understand with dijkstra is I can return successor with weight for a coordinate. But I can’t increase weight of diagonal/lateral according to previous position (ex: comming from West -> moving to Est will be less weight; maybe it is not the solution …).

How can I achieve that ? With another algorithm than dijkstra ?

user behavior – Is there any research showing whether relative or absolute times are easier for humans to read?

When you use absolute timestamps, you need to find out which timezone the user is in. This can be a guess based on IP address (which may be wrong) or a setting in the user profile (which needs to be filled out during user signup, adding another field), or a mix between both.

It is confusing to the user when you get it wrong. When I travel abroad, some websites show timestamps in my home timezone and others use the one where I currently am. The daylight savings switch in autumn will also show timestamps between 2AM and 3AM seemingly out of order, or imply that only a few minutes passed between two entries, when it was really an hour.

StackOverflow shows relative time for recent posts and switches to absolute (date only) after some time, when no one cares about the time of day a post was made anymore. What is best for your website depends on your application, whether there will be access to old material, whether users need to follow nonlinear reply chains, …

Google Sheets: How do I identify text in a column of cells and use a relative cell for addition if specific text is found?

I am looking to make a function that finds if a name (string) is in a particular cell, and then add the cell 2 columns over every time that specific name is found.

I think im looking for a type of if statement that identifies if any of cells from x to y have a specific string, then adding all the cells 2 to the right of every time the string is identified.

Specifically, I am calculating run differential in a baseball stat keeping sheet. I currently have it set up so The columns of the game logs are as follows: WINNER, LOSER, RUN DIFFERENTIAL. I am looking to create a function which finds the run differential each time that specific team played.

Example: if Team A won by 9 in their first game, the name of their team would be in the WINNER column, followed by the team they beat in the LOSER column, and then finally followed by the RUN DIFFERENTIAL (+9) which shows how much Team A, the WINNER, beat TEAM B by, the loser.

I think the idea is to first locate where the team won and lost. A team’s name in the win column indicates a win and is easy to identify, so my initial idea was to give a simple if statement for wins (If name is found in this cell, ADD the run differential which is 2 rows over, meaning they won by that much) and then sum whatever that number comes to with all the times they’re found in the loser column and the run differential of all those times, which would be how much they’ve lost by.