英文:
SQL or DAX Count values in column based on their position
问题
"may I ask you for help?"
"How to count values based on their position?"
可以请您帮忙吗？
如何根据位置来计算值？
"I need to have result like this:"
我需要结果如下：
"x 3"
"y 2"
"x 2"
"y 1"
"Thank you!!!"
谢谢！！！
"I am using this SQL script but it does not work..."
我正在使用这个SQL脚本，但它不起作用...
英文:
may I ask you for help?
How to count values based on their position?
column1
x
x
x
y
y
x
x
y
I need to have result like this:
x 3
y 2
x 2
y 1
Thank you!!!
I am using this SQL script but it does not work...
with test as (
select *,row_number() over (order by (select null)) as countt
FROM [vf_test_v02] as t
)
select column1,
ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY (select null)) as seqnum
from test
group by column1,countt
ORDER BY countt
答案1
得分: 1
你应该始终提供排序顺序，否则会浪费宝贵的时间。
你可以在SQL中按照以下方式操作：
CREATE TABLE vf_test_v02
("column1" varchar(1))
;
INSERT INTO vf_test_v02
("column1")
VALUES
('x'),
('x'),
('x'),
('y'),
('y'),
('x'),
('x'),
('y')
;
8 行受影响
with test as (
select column1, row_number() over (order by (select null)) as rn
FROM [vf_test_v02] as t
), LG as (SELECT
rn, column1, CASE WHEN column1 <> LAG("column1") OVER(order by rn) THEN 1 ELSE 0 END lg
FROM test), SU as (
SELECT
column1, SUM(lg) OVER(order by rn) su
FROM LG)
SELECT
column1, COUNT(*) countt
FROM SU
GROUP BY column1, su
英文:
You should always pprovide a sorting order, else it costs precious time.
You can do in SQL following
CREATE TABLE vf_test_v02
("column1" varchar(1))
;
INSERT INTO vf_test_v02
("column1")
VALUES
('x'),
('x'),
('x'),
('y'),
('y'),
('x'),
('x'),
('y')
;
8 rows affected
with test as (
select column1,row_number() over (order by (select null)) as rn
FROM [vf_test_v02] as t
), LG as (SELECT
rn,column1, CASE WHEN column1 <> LAG("column1") OVER(order by rn) THEN 1 ELSe 0 END lg
FROM test), SU as (
SELECT
column1, SUM(lg) OVER(order by rn) su
FROM LG)
SELECT
column1, COUNT(*) countt
FROM SU
GROUP BY column1,su
| column1 | countt |
|---|---|
| x | 3 |
| y | 2 |
| x | 2 |
| y | 1 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论