Running Total Using Self Join

I have got a table here:

———–+————+——–+ | Company | Date | Amount | +———–+————+——–+ | A | 22/07/2019 | 10 | | A | 22/07/2019 | 5 | | A | 23/07/2019 | 20 | | A | 23/07/2019 | 5 | | B | 22/07/2019 | 20 | | B | 22/07/2019 | 25 | | B | 23/07/2019 | 20 | | B | 23/07/2019 | 2 | | B | 23/07/2019 | 8 | +———–+————+——–+

I want a running total at a company level and a date level. I want to write the query using a SELF JOIN. I have tried writing the query using Window function.

SELECT company, date, SUM(amount), SUM(SUM(amount)) OVER (ORDER BY date)running_total FROM report GROUP BY
company, date ORDER BY date;

The final result should look like:

+———–+————+——–+————–+ | Company | Date | Amount | RunningTotal | +———–+————+——–+————–+ | A | 22/07/2019 | 10 | 15 | | A | 23/07/2019 | 25 | 40 | | B | 22/07/2019 | 45 | 45 | | B | 23/07/2019 | 30 | 75 | +———–+————+——–+————–+

Not able to join worker nodes using kubectl with updated aws-auth configmap

I’m setting up AWS EKS cluster using terraform from an EC2 instance. Basically the setup includes EC2 launch configuration and autoscaling for worker nodes. After creating the cluster, I am able to configure kubectl with aws-iam-authenticator. When I did

kubectl get nodes  

It returned

No resources found

as the worker nodes were not joined. So I tried updating aws-auth-cm.yaml file

apiVersion: v1 kind: ConfigMap metadata:   name: aws-auth   namespace: kube-system data:   mapRoles: |     - rolearn: <ARN of instance role (not instance profile)>       username: system:node:{{EC2PrivateDNSName}}       groups:         - system:bootstrappers         - system:nodes 

with IAM role ARN of the worker node. And did

kubectl apply -f aws-auth-cm.yaml 

It returned

ConfigMap/aws-auth created

Then I understood that role ARN configured in aws-auth-cm.yaml is the wrong one. So I updated the same file with the exact worker node role ARN.

But this time I got 403 when I did kubectl apply -f aws-auth-cm.yaml again.

It returned

Error from server (Forbidden): error when retrieving current configuration of: Resource: “/v1, Resource=configmaps”, GroupVersionKind: “/v1, Kind=ConfigMap” Name: “aws-auth”, Namespace: “kube-system” Object: &{map[“apiVersion”:”v1″ “data”:map[“mapRoles”:”- rolearn: arn:aws:iam::XXXXXXXXX:role/worker-node-role\n username: system:node:{{EC2PrivateDNSName}}\n groups:\n – system:bootstrappers\n – system:nodes\n”] “kind”:”ConfigMap” “metadata”:map[“name”:”aws-auth” “namespace”:”kube-system” “annotations”:map[“kubectl.kubernetes.io/last-applied-configuration”:””]]]} from server for: “/home/username/aws-auth-cm.yaml”: configmaps “aws-auth” is forbidden: User “system:node:ip-XXX-XX-XX-XX.ec2.internal” cannot get resource “configmaps” in API group “” in the namespace “kube-system”

I’m not able to reconfigure the ConfigMap after this step.

I’m getting 403 for commands like

kubectl apply kubectl delete kubectl edit  

for configmaps. Any help?

Postgresql using hash join with small table

I have one view (B) that returns ~20M records and a table (A) that has ~50M records. If I do A inner join B ON A.id=B.id, it performs a hash join, as expected. If I do B WHERE B.id IN ('value','value','value') it performs a nested loop with index scan on B, as expected. But if I do B WHERE B.id IN (SELECT id FROM A LIMIT 3) it performs a hash join, degrading performance terribly. Why is that? I tried disabling hash joins, but it is even worse: it uses seqscans anyway on both sides.

Doctrine DQL inner join on

Этот нативный sql делает нужную выборку.

SELECT r.* from routines r   inner join training_programs_routines tpr on r.id = tpr.routines_id   inner join categories_routines cr on r.id = cr.routines_id   where cr.categories_id = 11 and tpr.training_programs_id = 4; 

Вот этот запрос составленный через createQueryBuilder выбирает все данные из таблицы routines, что в корне не то, что нужно.

$  routines = $  qb                     ->from(Routine::class, 'r')                     ->innerJoin(TrainingProgram::class, 'tp')                     ->innerJoin(Category::class, 'c')                     ->andWhere("c.id = :cid")                     ->andWhere("tp.id = :pid")                     ->setParameters([                         'cid' => $  category->getId(),                         'pid' => $  program->getId()                     ])                     ->getQuery()                     ->getResult(); 

Посмотрел логи проблема в том, что sql составленный выглядит вот так

SELECT r0_.id AS id_0, r0_.name AS name_1, r0_.seconds AS seconds_2, r0_.count AS count_3, r0_.weight AS weight_4, r0_.distance AS distance_5, r0_.type AS type_6 FROM routines r0_  INNER JOIN training_programs t1_  INNER JOIN categories c2_, routines r3_  WHERE (c2_.id = ? AND t1_.id = ?)  AND (r0_.type IN ('static', 'repeat', 'run')  AND r3_.type IN ('static', 'repeat', 'run')) [10,4] []  

В inner join составленный DQL нет, условий ON.

Как дописать это условие, что бы верно все было?

SQLLite (Синтаксис WHERE и использование JOIN)

Дана таблица (House_EKB) с шириной (Width) и длиной (length) жилых участков (Number_of_House). Как найти все номера домов, у которых ширина больше средней ширины всех домов, но не используя подзапрос, скорее всего с помощью JOIN?

Пробовала сделать через WHERE House_EKB1.width > AVG (House_EKB2.width). Но SQL выдает ошибку неверного использования функции. Возможно ли использовать WHERE в синтасисе с другими функциями?